After hours of internet searching, I have found several examples of dynamic pivot techniques, but I am having trouble making any of them work for my situation.
Given this data:
I would like this output:
I don't care what the 0 values display (0, blank, N/A, etc.), and I realize I may need to generate the final output in ASP.NET (which is how it will be displayed to the customer). My question here is how to get multiple aggregates within the same pivot column (or fake it). Thanks.
asked Jan 26 '10 at 01:57 PM in Default
I haven't had the chance to work with this yet - but it seems to me you're starting with a pivoted data set - so if you unpivot that set first, so you end up with the columns:
Then you could re-pivot the set back to what you wanted...
The values in the DateAndFunction column would want to be something like 'January_Sales', 'February_Sales' etc...
edit -> I have now had the time to look at it properly:
Note that the columns don't come out in a pretty order, but, given the nature of PIVOTs (i.e. unknown columns based on data) then I think that's better handled by the client anyway.
No chance of doing this at the client? Where is the data being used, if its SSRS or something similar then it will be infinitely easier...
answered Jan 27 '10 at 05:14 PM
Tom, thanks for adding (or fake it) to your requirements :)
;WITH cte AS ( SELECT 1 AS month_num, 'Steve' AS customer, 50.00 AS purchase_amt, 10.00 AS discount_amt UNION SELECT 1, 'Mary', 45.00, 9.00 UNION SELECT 1, 'Mary', 15.00, 0.00 UNION SELECT 2, 'Mark', 40.00, 5.00 ) ,cte2 AS ( SELECT Customer,STUFF(REPLICATE(' ',300),month_Num * 20,20, CAST(Purchase_Amt AS char(10)) +CAST(Discount_Amt AS char(10))) [d] FROM cte ) SELECT Customer,SUM(CAST('0'+SUBSTRING(d,20,10) AS decimal(10,2)))[Purchase],SUM(CAST('0'+SUBSTRING(d,30,10) AS decimal(10,2)))[Discount] ,SUM(CAST('0'+SUBSTRING(d,40,10) AS decimal(10,2)))[Purchase],SUM(CAST('0'+SUBSTRING(d,50,10) AS decimal(10,2)))[Discount] FROM cte2 GROUP BY Customer