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
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
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