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.