Year and Month aggregation in same Pivot table in SQL Server

manish verma 421 Reputation points
2021-05-19T15:02:59.477+00:00

Hi All,
i have sample data like

Year Month CompanyID productBrand Product ProductType ActualCost
2019 004 P1010 R1010 M1010 P1010 345
2020 004 P1010 R1010 M1010 P1010 234
2021 004 P1010 R1010 M1010 P1010 100
2019 003 P1013 R1013 M1013 P1013 123
2020 004 P1012 R1012 M1012 P1012 421

output

97840-output.png

how we write this query static as well as dynamic using pivot

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,403 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,532 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-05-20T02:35:40.673+00:00

    Hi @manish verma ,

    Welcome to Microsoft Q&A!

    Please refer below static query:

    drop table if exists mytable  
      
    create table mytable  
    (Year int,  
    Month varchar(3),  
    CompanyID varchar(10),  
    productBrand varchar(10),  
    Product varchar(10),  
    ProductType varchar(10),  
    ActualCost int)  
      
    insert into mytable values  
    (2019,'004','P1010','R1010','M1010','P1010',345),  
    (2020,'004','P1010','R1010','M1010','P1010',234),  
    (2021,'004','P1010','R1010','M1010','P1010',100),  
    (2019,'003','P1013','R1013','M1013','P1013',123),  
    (2020,'004','P1012','R1012','M1012','P1012',421)  
      
    ;with cte as (  
    select cast(year as char(4))+'-'+cast(month as char(3)) yearmonth from  
    (select distinct year from mytable) a cross apply  
    (select distinct month from mytable) b)  
    ,cte1 as (select a.yearmonth,b.year,b.month,b.CompanyID,b.Product, ActualCost from cte a  
    left join (select cast(year as char(4))+'-'+cast(month as char(3)) yearmonth,year,month,  
    CompanyID,Product,ActualCost from mytable) b   
    on a.yearmonth=b.yearmonth)  
    select CompanyID,Product  
    , isnull([2019-003],0)+isnull([2019-004],0) [2019]  
    , isnull([2020-003],0)+isnull([2020-004],0) [2020]  
    , isnull([2021-003],0)+isnull([2021-004],0) [2021]  
    , isnull([2019-003],0) [2019-003]  
    , isnull([2020-003],0) [2020-003]  
    , isnull([2021-003],0) [2021-003]  
    , isnull([2019-004],0) [2019-004]  
    , isnull([2020-004],0) [2020-004]  
    , isnull([2021-004],0) [2021-004]  
     from  
    (select * from cte1) s  
    pivot  
    (max(ActualCost) for yearmonth in ([2019-003],[2020-003],[2021-003],[2019-004],[2020-004],[2021-004])) p  
    where year is not null  
    

    Output:

    CompanyID	Product	2019	2020	2021	2019-003	2020-003	2021-003	2019-004	2020-004	2021-004  
    P1013	M1013	123	0	0	123	0	0	0	0	0  
    P1010	M1010	345	0	0	0	0	0	345	0	0  
    P1010	M1010	0	234	0	0	0	0	0	234	0  
    P1012	M1012	0	421	0	0	0	0	0	421	0  
    P1010	M1010	0	0	100	0	0	0	0	0	100  
    

    Please refer below dynamic query:

    drop table if exists #table   
      
    select year,cast(year as char(4))+'-'+cast(month as char(3)) yearmonth   
    into #table  
    from  
    (select distinct year from mytable) a cross apply  
    (select distinct month from mytable) b  
      
    declare @sql nvarchar(max)  
    declare @s1 nvarchar(max)  
    declare @s2 nvarchar(max)  
    declare @s3 nvarchar(max)  
      
    select @s1=STUFF((select',['+ yearmonth+']'  from #table  FOR XML PATH('') ), 1, 1, '')   
      
    select @s2=STUFF((select',isnull([' +yearmonth+'],0) ['+ yearmonth+']'  from #table FOR XML PATH('') ), 1, 1, '')   
      
    select @s3= STUFF((select ',isnull(['+yearmonth+'],0)+isnull(['+yearmonth1+'],0) ['+cast(Year as char(4))+']' from (  
    select a.year,a.yearmonth,b.yearmonth yearmonth1,ROW_NUMBER() over (partition by a.year order by a.yearmonth) rn from #table a   
    inner join #table b   
    on a.Year=b.year   
    where  a.yearmonth<>b.yearmonth) a  
    where rn=1   
    FOR XML PATH('') ), 1, 1, '')  
      
    select @sql=N';with cte as (  
    select a.yearmonth,b.year,b.month,b.CompanyID,b.Product, ActualCost from #table a  
    left join (select cast(year as char(4))+''-''+cast(month as char(3)) yearmonth,year,month,  
    CompanyID,Product,ActualCost from mytable) b   
    on a.yearmonth=b.yearmonth)  
    select CompanyID,Product  
    ,'+@s3+','+@s2+' from  
    (select * from cte) s pivot  
    (max(ActualCost) for yearmonth in ('+@s1+')) p  
    where year is not null'  
      
    EXECUTE sp_executesql  @sql  
    

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments

5 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 100.1K Reputation points MVP
    2021-05-19T21:45:52.727+00:00

    I have a primer on how to write static and dynamic pivot queries here: https://www.sommarskog.se/dynamic_sql.html#pivot

    0 comments No comments

  2. manish verma 421 Reputation points
    2021-05-20T07:25:08.887+00:00

    Hi Melissa,

    i really appreciate your effort, if you don't mind can you introduce another measures [Productcost] measures with some dummy measures values in your solution.

    i wait for some time if not possible then i will accept this and close this thread

    Regards,
    Manish


  3. MelissaMa-MSFT 24,176 Reputation points
    2021-05-20T07:42:09.717+00:00

    Hi @manish verma ,

    Thanks for your update.

    You could also use max(case when...) instead of pivot as below:

    ;with cte as (  
    select cast(year as char(4))+'-'+cast(month as char(3)) yearmonth from  
    (select distinct year from mytable) a cross apply  
    (select distinct month from mytable) b)  
    ,cte1 as (select a.yearmonth,b.year,b.month,b.CompanyID,b.Product, ActualCost from cte a  
    left join (select cast(year as char(4))+'-'+cast(month as char(3)) yearmonth,year,month,  
    CompanyID,Product,ActualCost from mytable) b   
    on a.yearmonth=b.yearmonth)  
    --,cte2 as (  
    select yearmonth,CompanyID,Product,  
    isnull(max(case when yearmonth = '2019-003' then ActualCost end),0) + isnull(max(case when yearmonth = '2019-004' then ActualCost end),0)  '2019',  
    isnull(max(case when yearmonth = '2020-003' then ActualCost end),0) + isnull(max(case when yearmonth = '2020-004' then ActualCost end),0)  '2020',  
    isnull(max(case when yearmonth = '2021-003' then ActualCost end),0) + isnull(max(case when yearmonth = '2021-004' then ActualCost end),0)  '2021',  
    isnull(max(case when yearmonth = '2019-003' then ActualCost end),0) '2019-003',  
    isnull(max(case when yearmonth = '2020-003' then ActualCost end),0) '2020-003',  
    isnull(max(case when yearmonth = '2021-003' then ActualCost end),0) '2021-003',  
    isnull(max(case when yearmonth = '2019-004' then ActualCost end),0) '2019-004',  
    isnull(max(case when yearmonth = '2020-004' then ActualCost end),0) '2020-004',  
    isnull(max(case when yearmonth = '2021-004' then ActualCost end),0) '2021-004'  
    from cte1  
    where CompanyID is not null  
    group by yearmonth,CompanyID,Product  
    

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  4. manish verma 421 Reputation points
    2021-05-20T09:05:44.347+00:00

    hi ,

    Thanks i will post a new question

    0 comments No comments