none
pivot aggregation RRS feed

  • 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   Y-Sales X-Sales
    cat1  30 30
    cat2   40 20

    Royal Thomas





    • Edited by Royal Thomas Thursday, September 19, 2019 11:54 PM
    Thursday, September 19, 2019 11:01 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) [Y-Sales]
    ,Sum(Case when cat1='Y' then sales else null end)  [X-Sales] 
    from @output 
    union all
    Select 'cat2' as category 
    ,Sum(Case when cat2='X' then sales else null end) [Y-Sales]
    ,Sum(Case when cat2='Y' then sales else null end)  [X-Sales]
    from @output
     
    

    • Marked as answer by Royal Thomas Friday, September 20, 2019 4:23 AM
    Friday, September 20, 2019 1:01 AM
    Moderator
  • --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) [X-Sales]
    ,SUM(case when col='Y' then d.Sales else  null end) [Y-Sales]
    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
    Friday, September 20, 2019 1:19 AM
    Moderator
  • 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[X-Sales],[Y]as[Y-Sales] 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.[X-Sales] from (
    select category,sum(sales)as [Y-Sales]
    from cte1
    where number='Y'
    group by category)a
    join (
    select category, sum(sales)as [X-Sales]
    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[X-Sales],[Y]as[Y-Sales] from (
    select * from cte4 pivot 
    (sum(sales1)for [number]in([X],[Y]))as pvt)d
    
    /*
    category   X-Sales     Y-Sales
    ---------- ----------- -----------
    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.


    Friday, September 20, 2019 4:50 AM

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) [Y-Sales]
    ,Sum(Case when cat1='Y' then sales else null end)  [X-Sales] 
    from @output 
    union all
    Select 'cat2' as category 
    ,Sum(Case when cat2='X' then sales else null end) [Y-Sales]
    ,Sum(Case when cat2='Y' then sales else null end)  [X-Sales]
    from @output
     
    

    • Marked as answer by Royal Thomas Friday, September 20, 2019 4:23 AM
    Friday, September 20, 2019 1:01 AM
    Moderator
  • --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) [X-Sales]
    ,SUM(case when col='Y' then d.Sales else  null end) [Y-Sales]
    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
    Friday, September 20, 2019 1:19 AM
    Moderator
  • 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) [Y-Sales]
    ,Sum(Case when cat1='X' then sales else null end)  [X-Sales] 
    from @output 
    union all
    Select 'cat2' as category 
    ,Sum(Case when cat2='Y' then sales else null end) [Y-Sales]
    ,Sum(Case when cat2='X' then sales else null end)  [X-Sales]
    from @output;

    Friday, September 20, 2019 3:48 AM
  • Thanks Jingyang & yitzhak

    Royal Thomas

    Friday, September 20, 2019 4:24 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[X-Sales],[Y]as[Y-Sales] 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.[X-Sales] from (
    select category,sum(sales)as [Y-Sales]
    from cte1
    where number='Y'
    group by category)a
    join (
    select category, sum(sales)as [X-Sales]
    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[X-Sales],[Y]as[Y-Sales] from (
    select * from cte4 pivot 
    (sum(sales1)for [number]in([X],[Y]))as pvt)d
    
    /*
    category   X-Sales     Y-Sales
    ---------- ----------- -----------
    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.


    Friday, September 20, 2019 4:50 AM
  • Thanks Sabirna

    Royal Thomas

    Friday, September 20, 2019 4:54 PM