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???