Answered by:
pivot aggregation
Question

Hi, how can I write a sql to acheive below result set:
declare @output table (cat1 varchar(5), cat2 varchar(5), sales int)
insert into @output
values ('Y', 'Y', 10),
('X', 'Y', 10),
('X', 'Y', 10),
('X', 'Y', 10),
('Y', 'X', 10),
('Y', 'X', 10)select * from @output
required results:
category YSales XSales cat1 30 30 cat2 40 20 Royal Thomas
 Edited by Royal Thomas Thursday, September 19, 2019 11:54 PM
Answers

declare @output table (cat1 varchar(5), cat2 varchar(5), sales int) insert into @output values ('Y', 'Y', 10), ('X', 'Y', 10), ('X', 'Y', 10), ('X', 'Y', 10), ('Y', 'X', 10), ('Y', 'X', 10) select 'cat1' as category ,Sum(Case when cat1='X' then sales else null end) [YSales] ,Sum(Case when cat1='Y' then sales else null end) [XSales] from @output union all Select 'cat2' as category ,Sum(Case when cat2='X' then sales else null end) [YSales] ,Sum(Case when cat2='Y' then sales else null end) [XSales] from @output
 Marked as answer by Royal Thomas Friday, September 20, 2019 4:23 AM

or this:
declare @output table (cat1 varchar(5), cat2 varchar(5), sales int) insert into @output values ('Y', 'Y', 10), ('X', 'Y', 10), ('X', 'Y', 10), ('X', 'Y', 10), ('Y', 'X', 10), ('Y', 'X', 10) select category ,SUM(case when col='X' then d.Sales else null end) [XSales] ,SUM(case when col='Y' then d.Sales else null end) [YSales] from @output cross apply ( values('cat1',cat1,sales),('cat2',cat2,sales)) d (category,col,sales) group by category
 Marked as answer by Royal Thomas Friday, September 20, 2019 4:23 AM

Hi Royal,
Here are anther 3 methods you can choose from:
create table [output] (cat1 varchar(5), cat2 varchar(5), sales int) insert into [output] values ('Y', 'Y', 10), ('X', 'Y', 10), ('X', 'Y', 10), ('X', 'Y', 10), ('Y', 'X', 10), ('Y', 'X', 10) method1 ;with cte1 as( select u.category,u.number,u.sales from output unpivot( number for category in (cat1,cat2))u), cte2 as ( select distinct category,number, sum(sales)over(partition by category,number)as total from cte1) select category, [X]as[XSales],[Y]as[YSales] from ( select * from cte2 pivot (sum(total)for [number]in([X],[Y]))as pvt)d method2 ;with cte1 as( select u.category,u.number,u.sales from output unpivot( number for category in (cat1,cat2))u) select a.*,b.[XSales] from ( select category,sum(sales)as [YSales] from cte1 where number='Y' group by category)a join ( select category, sum(sales)as [XSales] from cte1 where number='X' group by category)b on a.category=b.category method3 ;with cte as ( select distinct u.category,u.number from output unpivot( number for category in (cat1,cat2))u), cte2 as ( select distinct cat1, sum(sales)over(partition by cat1)as [Sales1] from output), cte3 as ( select distinct cat2, sum(sales)over(partition by cat2)as [Sales2] from output), cte4 as ( select a.*,b.sales1 from cte a join ( select * from cte2)b on a.number=b.cat1 and a.category='cat1' union select a.*,c.sales2 from cte a join ( select * from cte3)c on a.number=c.cat2 and a.category='cat2') select category, [X]as[XSales],[Y]as[YSales] from ( select * from cte4 pivot (sum(sales1)for [number]in([X],[Y]))as pvt)d /* category XSales YSales    cat1 30 30 cat2 20 40 */
Hope it could help.
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com. Edited by Sabrina ZhangMicrosoft contingent staff Friday, September 20, 2019 4:50 AM
 Marked as answer by Royal Thomas Friday, September 20, 2019 4:53 PM
All replies

declare @output table (cat1 varchar(5), cat2 varchar(5), sales int) insert into @output values ('Y', 'Y', 10), ('X', 'Y', 10), ('X', 'Y', 10), ('X', 'Y', 10), ('Y', 'X', 10), ('Y', 'X', 10) select 'cat1' as category ,Sum(Case when cat1='X' then sales else null end) [YSales] ,Sum(Case when cat1='Y' then sales else null end) [XSales] from @output union all Select 'cat2' as category ,Sum(Case when cat2='X' then sales else null end) [YSales] ,Sum(Case when cat2='Y' then sales else null end) [XSales] from @output
 Marked as answer by Royal Thomas Friday, September 20, 2019 4:23 AM

or this:
declare @output table (cat1 varchar(5), cat2 varchar(5), sales int) insert into @output values ('Y', 'Y', 10), ('X', 'Y', 10), ('X', 'Y', 10), ('X', 'Y', 10), ('Y', 'X', 10), ('Y', 'X', 10) select category ,SUM(case when col='X' then d.Sales else null end) [XSales] ,SUM(case when col='Y' then d.Sales else null end) [YSales] from @output cross apply ( values('cat1',cat1,sales),('cat2',cat2,sales)) d (category,col,sales) group by category
 Marked as answer by Royal Thomas Friday, September 20, 2019 4:23 AM

Hi Jingyang Li,
There is a small error in the first method.
Here is the adjusted version. All credit goes to you.
declare @output table (cat1 varchar(5), cat2 varchar(5), sales int) insert into @output values ('Y', 'Y', 10), ('X', 'Y', 10), ('X', 'Y', 10), ('X', 'Y', 10), ('Y', 'X', 10), ('Y', 'X', 10); select 'cat1' as category ,Sum(Case when cat1='Y' then sales else null end) [YSales] ,Sum(Case when cat1='X' then sales else null end) [XSales] from @output union all Select 'cat2' as category ,Sum(Case when cat2='Y' then sales else null end) [YSales] ,Sum(Case when cat2='X' then sales else null end) [XSales] from @output;


Hi Royal,
Here are anther 3 methods you can choose from:
create table [output] (cat1 varchar(5), cat2 varchar(5), sales int) insert into [output] values ('Y', 'Y', 10), ('X', 'Y', 10), ('X', 'Y', 10), ('X', 'Y', 10), ('Y', 'X', 10), ('Y', 'X', 10) method1 ;with cte1 as( select u.category,u.number,u.sales from output unpivot( number for category in (cat1,cat2))u), cte2 as ( select distinct category,number, sum(sales)over(partition by category,number)as total from cte1) select category, [X]as[XSales],[Y]as[YSales] from ( select * from cte2 pivot (sum(total)for [number]in([X],[Y]))as pvt)d method2 ;with cte1 as( select u.category,u.number,u.sales from output unpivot( number for category in (cat1,cat2))u) select a.*,b.[XSales] from ( select category,sum(sales)as [YSales] from cte1 where number='Y' group by category)a join ( select category, sum(sales)as [XSales] from cte1 where number='X' group by category)b on a.category=b.category method3 ;with cte as ( select distinct u.category,u.number from output unpivot( number for category in (cat1,cat2))u), cte2 as ( select distinct cat1, sum(sales)over(partition by cat1)as [Sales1] from output), cte3 as ( select distinct cat2, sum(sales)over(partition by cat2)as [Sales2] from output), cte4 as ( select a.*,b.sales1 from cte a join ( select * from cte2)b on a.number=b.cat1 and a.category='cat1' union select a.*,c.sales2 from cte a join ( select * from cte3)c on a.number=c.cat2 and a.category='cat2') select category, [X]as[XSales],[Y]as[YSales] from ( select * from cte4 pivot (sum(sales1)for [number]in([X],[Y]))as pvt)d /* category XSales YSales    cat1 30 30 cat2 20 40 */
Hope it could help.
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com. Edited by Sabrina ZhangMicrosoft contingent staff Friday, September 20, 2019 4:50 AM
 Marked as answer by Royal Thomas Friday, September 20, 2019 4:53 PM
