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:
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
I would like this output:
January February Customer Purchase Discount Purchase Discount Mark 0.00 0.00 40.00 5.00 Mary 60.00 9.00 0.00 0.00 Steve 50.00 10.00 0.00 0.00
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.