TSQL PIVOT/UNPIVOT Multiple Summarized Columns

I would use cross apply to unpivot, and then conditional aggregation to pivot:

select x.category,
    sum(case when t.month = 1 then val end) month_1,
    sum(case when t.month = 2 then val end) month_2,
    sum(case when t.month = 3 then val end) month_3,
    sum(case when t.month = 4 then val end) month_4,
    sum(case when t.month = 5 then val end) month_5,
    sum(case when t.month = 6 then val end) month_6
from @summarizedtable t
cross apply (values 
    ('calls', t.calls), 
    ('deals', t.deals), 
    ('productssold', t.productssold),
    ('avgsaleprice', t.avgsaleprice)
) as x(category, val)
group by x.category

Demo on DB Fiddle:

category     | month_1 | month_2 | month_3 | month_4 | month_5 | month_6
:----------- | ------: | ------: | ------: | ------: | ------: | ------:
avgsaleprice |     500 |     300 |     600 |     800 |     300 |     250
calls        |      25 |      17 |      15 |      22 |      18 |      12
deals        |       6 |       2 |       3 |       1 |       7 |       9
productssold |       7 |       4 |       5 |       1 |      12 |      15

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top