TSQL PIVOT/UNPIVOT Multiple Summarized Columns

Question

Seems this is a popular topic but I can't seem to replicate other answers.

I have data that is already summarized by month/year for multiple categories.

month   calls   deals   productssold   avgsaleprice 
    1      25       6              7            500
    2      17       2              4            300
    3      15       3              5            600
    4      22       1              1            800
    5      18       7             12            300
    6      12       9             15            250

What I'm trying to get is this

    category   1   2   3   4   5   6
       calls  25  17  15  22  18  12
       deals   6   2   3   1   7   9
productssold   7   4   5   1  12  15
avgsaleprice 500 300 600 800 300 250

Here's the query to provide the data

declare @summarizedtable table (
 month int,
 calls int,
 deals int,
 productssold int,
 avgsaleprice int)    
 
 insert into @summarizedtable (month, calls, deals, productssold, avgsaleprice)
 values (1,25,6,7,500),
        (2,17,2,4,300),
        (3,15,3,5,600),
        (4,22,1,1,800),
        (5,18,7,12,300),
        (6,12,9,15,250);
        
 select *
   from @summarizedtable

I've used PIVOT a bunch of times, but with a single "category" of data. I can't seem to get anything to stick using multiple categories

What *did* you try? Why didn't it work? Personally, I suggest using conditional aggregation over the more restrictive `PIVOT` operator. — Thom A, Oct 28 '20 at 15:52

score 1 · Accepted Answer · answered Oct 28 '20 at 15:53

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

TSQL PIVOT/UNPIVOT Multiple Summarized Columns

1 Answers1