troubleshooting Question

MSSQL Consolidate Records in Select Statement

Avatar of trbbhm
trbbhmFlag for Afghanistan asked on
Microsoft SQL Server 2008
1 Comment1 Solution329 ViewsLast Modified:
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???

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros