question

manishverma-7371 avatar image
0 Votes"
manishverma-7371 asked manishverma-7371 answered

Year and Month aggregation in same Pivot table in SQL Server

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

sql-server-generalsql-server-transact-sqlazure-sql-database
output.png (4.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered

Hi @manishverma-7371,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

manishverma-7371 avatar image
0 Votes"
manishverma-7371 answered MelissaMa-msft edited

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @manishverma-7371,

introduce another measures [Productcost] measures with some dummy measures values in your solution.

Could you please provide more details about it?

Or you could post a new question and provide more sample data and expected output.

Best regards,
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @manishverma-7371,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

manishverma-7371 avatar image
0 Votes"
manishverma-7371 answered

hi ,

Thanks i will post a new question

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

manishverma-7371 avatar image
0 Votes"
manishverma-7371 answered

Hi Melissa,

i have post a new question related with this question, if possible please see this question.

https://docs.microsoft.com/en-us/answers/questions/402689/how-we-handle-multiple-measers-with-year-and-month.html

Thanks a lot for your help and support .


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.