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.

podiluska · Accepted Answer · 2013-10-21 07:53:18Z

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.

edited Oct 21, 2013 at 7:53

answered Oct 21, 2013 at 7:31

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LeO Over a year ago

work good, the point i was missing was cost and paytype when i was trying to unpivot. Thanks...!!

Collectives™ on Stack Overflow

Pivot on Multiple Columns in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related