How we handle multiple Measers with Year and Month aggeration dynamically in Pivot Table in SQL Server

Question

Hi All,

i have sample data

Year Period Companyid ProductBrand Product ProductType ActualCost netprofit
2019 004 P1010 R1010 M1010 P1010 345 450
2020 004 P1010 R1010 M1010 P1010 234 300
2021 004 P1010 R1010 M1010 P1010 100 98
2019 003 P1013 R1013 M1013 P1013 123 432
2020 004 P1012 R1012 M1012 P1012 421 234

here ActualCost and netprofit is measure.

and requirement is dynamically pivot , but we are only do static pivot, please see below code

SELECT *  into #temp  FROM     
(  
    SELECT   
	     
	    Concat([Year],'-','ActualCost') as [YearActualCost],  
		Concat([Year],'-','netprofit') as [Yearnetprofit],  
	    Concat([Year],'-', [Period],'-','ActualCost') as [Date],  
        Concat([Year],'-', [Period],'-','netprofit') as [netprofitDate],  
		 --Concat([Year],'-', [Period]) as [netprofitDate],  
       Companyid,  
         Prdoduct,   
		ActualCost,   
		ActualCost as MonthActualCost,  
		netprofit,  
		netprofit as Monthnetprofit  
    FROM [StageDatabase].[dbo].[Cost]  
) t   
  
PIVOT(Sum([ActualCost]) for [YearActualCost] in([2019-ActualCost], [2020-ActualCost], [2021-ActualCost])) AS pivot_table0  
PIVOT(Sum([netprofit]) for [Yearnetprofit] in([2019-netprofit], [2020-netprofit], [2021-netprofit])) AS pivot_table1  
PIVOT(Sum([MonthActualCost]) for [Date] in([2019-003-ActualCost], [2020-003-ActualCost], [2021-003-ActualCost], [2019-004-ActualCost],	 [2020-004-ActualCost], [2021-004-ActualCost])) AS pivot_table2  
PIVOT(Sum([Monthnetprofit]) for [netprofitDate] in([2019-003-netprofit], [2020-003-netprofit], [2021-003-netprofit], [2019-004-netprofit],	 [2020-004-netprofit], [2021-004-netprofit])) AS pivot_table3;  
  
  
select Companyid,Product,  
  
isnull([2019-ActualCost],0) as  [2019-ActualCost],  
isnull([2020-ActualCost],0) as [2020-ActualCost] ,  
isnull ([2021-ActualCost],0) as [2021-ActualCost],  
isnull([2019-netprofit],0) as [2019-netprofit],  
isnull([2020-netprofit],0) as [2020-netprofit],  
isnull([2021-netprofit],0) as [2021-netprofit],  
isnull([2019-003-ActualCost],0) as [2019-003-ActualCost],  
isnull([2020-003-ActualCost],0) as [2020-003-ActualCost],  
isnull([2021-003-ActualCost],0) as [2021-003-ActualCost],  
 isnull([2019-004-ActualCost],0) as [2019-004-ActualCost],  
isnull([2020-004-ActualCost], 0) as [2020-004-ActualCost],  
isnull([2021-004-ActualCost],0) as [2021-004-ActualCost],  
isnull([2019-003-netprofit],0) as  [2019-003-netprofit],  
isnull([2020-003-netprofit],0) as [2020-003-netprofit],   
isnull([2021-003-netprofit],0) as [2020-003-netprofit] ,  
isnull([2019-004-netprofit],0) as  [2019-004-netprofit],  
isnull([2020-004-netprofit],0) as [2019-004-netprofit],  
isnull([2021-004-netprofit],0) as [2019-004-netprofit]  
  
  
from #temp  
  

 

output

Answer 1

Hi @manish verma ,

Please refer below:

drop table if exists  #temp1  
  select distinct *   
  into #temp1  
  from (  
  (select distinct year  FROM mytable) a  
  cross apply (select distinct Period  FROM mytable) b  
  cross apply (select 'ActualCost' measure union select 'netprofit' ) c)   
  
 declare @sql nvarchar(max)  
 declare @s1 nvarchar(max)  
 declare @s2 nvarchar(max)  
 declare @s3 nvarchar(max)  
 declare @s4 nvarchar(max)  
 declare @s5 nvarchar(max)  
 declare @s6 nvarchar(max)  
 declare @s7 nvarchar(max)  
 declare @s8 nvarchar(max)  
  
  
 select @s1=STUFF((select distinct ',['+ cast(year as char(4))+'-'+ measure+']'  from #temp1 where measure='ActualCost'   
 FOR XML PATH('') ), 1, 1, '')   
      
 select @s2=STUFF((select distinct ',isnull([' +cast(year as char(4))+'-' +measure+'],0) ['+ cast(year as char(4))+ measure++']'    
 from #temp1 where measure='ActualCost' FOR XML PATH('') ), 1, 1, '')   
  
  select @s3=STUFF((select distinct ',['+ cast(year as char(4))+'-'+ measure+']'  from #temp1 where measure='netprofit'   
 FOR XML PATH('') ), 1, 1, '')   
      
 select @s4=STUFF((select distinct ',isnull([' +cast(year as char(4))+'-' +measure+'],0) ['+ cast(year as char(4))+ measure++']'    
 from #temp1 where measure='netprofit' FOR XML PATH('') ), 1, 1, '')   
  
  select @s5=STUFF((select distinct ',['+ cast(year as char(4))+'-'+cast(Period as char(3))+'-'+ measure+']'  from #temp1 where measure='ActualCost'   
 FOR XML PATH('') ), 1, 1, '')   
  
 select @s6=STUFF((select distinct ',isnull([' +cast(year as char(4))+'-'+cast(Period as char(3))+'-' +measure+'],0) ['+ cast(year as char(4))+'-'+cast(Period as char(3))+ measure++']'    
 from #temp1 where measure='ActualCost' FOR XML PATH('') ), 1, 1, '')   
  
  select @s7=STUFF((select distinct ',['+ cast(year as char(4))+'-'+cast(Period as char(3))+'-'+ measure+']'  from #temp1 where measure='netprofit'   
 FOR XML PATH('') ), 1, 1, '')   
  
 select @s8=STUFF((select distinct ',isnull([' +cast(year as char(4))+'-'+cast(Period as char(3))+'-' +measure+'],0) ['+ cast(year as char(4))+'-'+cast(Period as char(3))+ measure++']'    
 from #temp1 where measure='netprofit' FOR XML PATH('') ), 1, 1, '')   
  
  
set @sql=N'  
 SELECT *  into #temp  FROM     
 (  
     SELECT   
             
         Concat([Year],''-'',''ActualCost'') as [YearActualCost],  
         Concat([Year],''-'',''netprofit'') as [Yearnetprofit],  
         Concat([Year],''-'', [Period],''-'',''ActualCost'') as [Date],  
         Concat([Year],''-'', [Period],''-'',''netprofit'') as [netprofitDate],  
        Companyid,  
          Product,   
         ActualCost,   
         ActualCost as MonthActualCost,  
         netprofit,  
         netprofit as Monthnetprofit  
     FROM mytable  
 ) t   
      
 PIVOT(Sum([ActualCost]) for [YearActualCost] in('+@s1+')) AS pivot_table0  
 PIVOT(Sum([netprofit]) for [Yearnetprofit] in('+@s3+')) AS pivot_table1  
 PIVOT(Sum([MonthActualCost]) for [Date] in('+@s5+')) AS pivot_table2  
 PIVOT(Sum([Monthnetprofit]) for [netprofitDate] in('+@s7+')) AS pivot_table3;  
      
      
 select Companyid,Product,'+@s2+','+@s4+','+@s6+','+@s8+'  
 from #temp'  
  
 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.

Answer 2

Thanks a lot Melissa for your effort.

i want to know some best practices to implement, pivot option,

we need to do analysis only 3 years of data.

report we need- YoY, MTD, LY-CY, i need some comment and limitation of Pivot options, to implement these types of reports

Regards,
Manish