question

manishverma-7371 avatar image
0 Votes"
manishverma-7371 asked MelissaMa-msft commented

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

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
98244-pivotoutput.png


sql-server-generalsql-server-transact-sql
pivotoutput.png (12.1 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
0 Votes"
MelissaMa-msft answered

Hi @manishverma-7371,

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.


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 commented

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

· 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,

Thanks for your update.

You could refer below two articles about dynamic pivot and check whether they are helpful to you.

T-SQL: Dynamic Pivot on Multiple Columns
SQL - Pivot with Grand Total Column and Row

If you still face any concern about this, you could post a new question and we will look further.

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·