Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Flattening the results of a query newbie question

Answered Flattening the results of a query newbie question

  • Thursday, March 01, 2012 12:18 AM
     
      Has Code
    Sign In to Vote
    0

    Hi all,

    I have this query

    SELECT     commbyrepID, agent, office, address, [property-type], [listing-source], [sale-status], [sale-date], [sale-price], [gross-comm], [comm-intro-amount], 
                      [comm-intro-precentage], [exp-settle-date], Listers, Managers, Sellers, initials, role
FROM         dbo.precalc_CommissionBySalesRep

    It returns the info as follows

    The only columns that are different in the record are the Listers Managers and Sellers columns and the role column (which isn't needed) and also the [comm-intro-percentage] and [comm-intro amount] rows which I need to sum up with the SUM() command i presume.

    I need each property per agent to be returned on a seperate row with the Listers Managers and Sellers all pushed into the one record so all those nulls are not there. (does that make sense?) Is it even possible? Thanks


    • Edited by Mordt Thursday, March 01, 2012 12:19 AM
    •  
     

All Replies

  • Thursday, March 01, 2012 12:37 AM
     
     Answered Has Code
    Sign In to Vote
    1
     
    SELECT     
	commbyrepID
	,agent
	,office
	,address
	,[property-type]
	,[listing-source]
	,[sale-status]
	,[sale-date]
	,[sale-price]
	,[gross-comm]
	,Sum([comm-intro-amount]) As [comm-intro-amount]
	,Sum([comm-intro-precentage]) As [comm-intro-precentage]
	,[exp-settle-date]
	,Max(IsNull(Listers, '')) As Listers
	,Max(IsNull(Managers, '')) As Managers
	,Max(IsNull(Sellers, '')) As Sellers 
	,initials
	--,role
FROM         
	dbo.precalc_CommissionBySalesRep
Group By 
	commbyrepID
	,agent
	,office
	,address
	,[property-type]
	,[listing-source]
	,[sale-status]
	,[sale-date]
	,[sale-price]
	,[gross-comm]
	,[exp-settle-date]
	,initials


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.


    • Edited by Arbi Baghdanian Thursday, March 01, 2012 12:38 AM
    • Marked As Answer by Mordt Thursday, March 01, 2012 12:59 AM
    • Unmarked As Answer by Mordt Thursday, March 01, 2012 1:25 AM
    • Marked As Answer by Mordt Thursday, March 01, 2012 1:26 AM
    •  
     
  • Thursday, March 01, 2012 12:55 AM
     
     
    Sign In to Vote
    0

    Hi Arbi,

    That's the way I thought at first but that doesn't work, all that does is remove the NULL's and leave blanks it doesn't flatten as expected under group by.

    Thanks

     
  • Thursday, March 01, 2012 1:00 AM
     
     
    Sign In to Vote
    0

    I removed the id field and it works a charm :)

    thanks

     
  • Thursday, March 01, 2012 1:25 AM
     
     
    Sign In to Vote
    0

    Ah i actually see an unforeseen issue with this and that if the property exists twice because two different agents are involved. the lsiter, manager and seller fields have empty values, where i want them to have the value of all the list manage and sell combined regardless of agent (basically there will be no blank spots anywhere. 

    So my question would it be more efficient to just create a new table with the lister manager and seller sections and join this table and the new table up

    or add selects for each of the Lister Manager and Seller Sections so I can get all the data for that field for that property regardless of agent.

    • Edited by Mordt Thursday, March 01, 2012 1:25 AM
    •  
     
  • Thursday, March 01, 2012 1:43 AM
     
     
    Sign In to Vote
    0
    Mordt, 
    I will personally will go with new table and then join it. But it depends on your UI. 

    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

     
  • Thursday, March 01, 2012 2:33 AM
     
      Has Code
    Sign In to Vote
    0

    Thanks for your help Arbi.

    How would I join these two selects together ? The [address] and [sale-date] fields are unique to the record in both results

    Query 1

    SELECT agent, office, address, [property-type], [listing-source], [sale-status], [sale-date], [sale-price], [gross-comm], SUM([comm-intro-amount]) AS [comm-intro-amount], SUM([comm-intro-precentage]) AS [comm-intro-precentage], [exp-settle-date]
FROM  precalc_CommissionBySalesRep
GROUP BY agent, office, address, [property-type], [listing-source], [sale-status], [sale-date], [sale-price], [gross-comm], [exp-settle-date], initials

    Query 2

    SELECT     address, [sale-date], MAX(ISNULL(Listers, '')) AS Listers, MAX(ISNULL(Managers, '')) AS Managers, MAX(ISNULL(Sellers, '')) AS Sellers
    FROM         precalc_CommissionBySalesRep
    GROUP BY address, [sale-date], [sale-price]

     
  • Thursday, March 01, 2012 2:37 AM
     
      Has Code
    Sign In to Vote
    0

    I think i have figured it out

    does this look right or Ok?

    SELECT *

FROM
(

SELECT agent, office, address, [property-type], [listing-source], [sale-status], [sale-date], [sale-price], [gross-comm], SUM([comm-intro-amount]) AS [comm-intro-amount], SUM([comm-intro-precentage]) AS [comm-intro-precentage], [exp-settle-date]
FROM  precalc_CommissionBySalesRep
GROUP BY agent, office, address, [property-type], [listing-source], [sale-status], [sale-date], [sale-price], [gross-comm], [exp-settle-date], initials) as t1 LEFT OUTER JOIN

(
SELECT     address, [sale-date], MAX(ISNULL(Listers, '')) AS Listers, MAX(ISNULL(Managers, '')) AS Managers, MAX(ISNULL(Sellers, '')) AS Sellers
FROM         precalc_CommissionBySalesRep
GROUP BY address, [sale-date], [sale-price]) as t2 ON t1.address = t2.address