Pivot on Multiple Columns in SQL Server

Question

I have following data.

state    f_name     product     paymonth     income     expense     savings
state_1  f1         product_1   apr-2013     5000       1500        3500
state_1  f1         procudt_1   may-2013     4500       3000        1500
state_1  f1         procudt_2   may-2013     4500       3000        1500
state_2  f2         product_2   apr-2013     15000      11500       35000
state_2  f2         product_3   aug-2013     1500       500         1000

Pivot on paymonth column with headers like these which is desired result...

state    f_name   product    apr-2013_income   apr-2013_expense   apr-2013_savings   may-2013_income   may-2013_expense.....and so on for distinct paymonth
state_1  f1       product_1  5000              1500               3500               4500              3000....and so on....
state_1  f1       product_2  0                 0                  0                  4500              3000....

Now, the real problem is state, f_name and products must remain as columns and they are dynamic.

Also paymonth and other data is dynamic.

Need to use Pivot. I googled some examples but not getting what i want.

This is what i tried...

DECLARE @COLS AS NVARCHAR(MAX) = ''

SELECT @COLS = COALESCE(@COLS + ', ', '') + QUOTENAME(PAYMONTH)
FROM #DATA
GROUP BY PAYMONTH, PM ORDER BY PM --pm is last date of paymonth for ordering data in asc/desc

declare @QUERY AS NVARCHAR(MAX) = ''

SET @QUERY = 'SELECT state, product, f_name, ' + @COLS + ' FROM
(
    SELECT state, product, f_name, income, paymonth--, expense, saving,
    FROM #DATA
) X
PIVOT
(
         SUM(income)
         FOR paymonth IN (' + @COLS + ')
) P
ORDER BY state, product, f_name'
execute(@query)

This will give correct result for pivot on paymonth for income but i cant do it for all 3, income, expense and savings.

Tried cross apply and unpivot but failed.

Need help in this. Thanks.

Answer 1

Your problem is the denormalised structure of your source table. As always, this leads to difficulties in querying.

If you normalise it using unpivot, then the next pivot is relatively simple...

select * 
from 
(
     select state,f_name,product, cost, paymonth+'_' +paytype as pp from #data
    unpivot (cost  for paytype in (income, expense, savings)) u
) up
pivot (sum(cost) for pp in 
    ([apr-2013_income],[apr-2013_expense],[apr-2013_savings],[may-2013_income] --, ....
    )
) p

Constructing dynamic SQL from this is left as an exercise for the reader.