Link to home
Create AccountLog in
Avatar of trbbhm
trbbhmFlag for Afghanistan

asked on

MSSQL Consolidate Records in Select Statement

Can someone help me construct a MSSQL statement that will return data in a consolidated form?  The MSSQL command that I'm currently using currently returns one row per customer per vendor as follows:

SELECT crm_Customer_Employee_Assignments.CustomerNum, crm_customerPurchaseRollup.custLevel,CUSTOMER.LastName, CUSTOMER.FirstName, crm_customer_purchaseHistoryBuckets.LifetimeSalesDollars,custsls.vendornum,sum(priceextension) as sales,count(priceextension) as trcount
FROM crm_Customer_Employee_Assignments
LEFT OUTER JOIN CUSTSLS ON crm_Customer_Employee_Assignments.CustomerNum=CUSTSLS.CustNum
INNER JOIN CUSTOMER ON crm_Customer_Employee_Assignments.CustomerNum = CUSTOMER.CustomerNum
INNER JOIN crm_customer_purchaseHistoryBuckets ON crm_customer_purchaseHistoryBuckets.CustomerNum=crm_Customer_Employee_Assignments.CustomerNum
LEFT OUTER JOIN crm_customerPurchaseRollup ON crm_Customer_Employee_Assignments.CustomerNum = crm_customerPurchaseRollup.trueCustNum
WHERE custsls.transdate>='2000-1-1' and custsls.transdate<='2012-12-21' AND (custsls.vendornum=3654 or custsls.vendornum=11691 or custsls.vendornum=62242) group by crm_Customer_Employee_Assignments.customernum,custlevel,CUSTOMER.LASTNAME,CUSTOMER.FIRSTNAME,custsls.vendornum,crm_customer_purchaseHistoryBuckets.LifetimeSalesDollars

This SQL statment returns data in the following manner....

<cust>     <lvl>     <fname>   <lname> <life$>      <vend#>  <vend$>  <vendct>
12345     Gold     Edward      Smith     1234.56     3654        50             3
12345     Gold     Edward      Smith     1234.56     62242      89             2
54545     Silver    Frank         Roger     1053.54     3654        80            5
54545     Silver    Frank         Roger     1053.54     11691      100          3
54545     Silver    Frank         Roger     1053.54     62242       54           5
89817     Gold      Jim            Jim         2432.22     11691      111          4

What I would like to do is to return one row per customer with vendor totals on a single row (return either 0 or null if there are no purchases for a particular vendor.  For instance, the data above would be returned as follows:

<c#>    <lvl>   <fn>      <ln>     <$$$>    <v$>    <vc>     <v$>    <vc>     <v$>   <vc>
12345  Gold    Edward  Smith  1234.56  50        3          89        2         <null>  <null>
54545  Silver  Frank     Roger  1053.54  80        5          54        5          100      3
89817  Gold    Jim        Jim      2432.22  <null>  <null>  <null>  <null>  111      4

Is this possible???
Avatar of trbbhm
Flag of Afghanistan image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account