making a pivot more flexible without using dynamic sql

Question

Hi we run 2014 STD. How can i abstract the hardcoded references to [2021] etc in the following pivot without introducing dynamic sql? somehow i'd like [2021] to reference @pYear, [2020] to reference @yr2 etc. somehow i'd like to keep the labels YR1, YR2...etc.

declare @pYear smallint=2021  
  
declare @yr2 smallint = @pYear-1  
declare @yr3 smallint = @pYear-2  
declare @yr4 smallint = @pYear-3  
declare @yr5 smallint = @pYear-4  
declare @yr6 smallint = @pYear-5  
  
SELECT field1, field2, field3, [2021] AS YR1, [2020] AS YR2, [2019] AS YR3, [2018] AS YR4, [2017] AS YR5, [2016] as YR6   
FROM     
(SELECT field1, field2, field3, amount, [year]   
FROM #temp) p    
PIVOT    
(    
max (amount)    
FOR [year] IN    
( [2021], [2020], [2019], [2018], [2017], [2016] )    
) AS pvt    
ORDER BY pvt.field1,pvt.field2,pvt.field3;    

Answer 1

Hi @db042190 ，

If your year has only the six you mentioned, then you can refer to Viorel's method. However, if you have more years, using dynamic SQL is an easier option.

If you have any question, please feel free to let me know.

Regards
Echo

Answer 2

Try something like this:

SELECT field1, field2, field3,
    max(case when [year] = @pYear then amount end) as YR1,
    max(case when [year] = @yr2 then amount end) as YR2,
    max(case when [year] = @yr3 then amount end) as YR3,
    max(case when [year] = @yr4 then amount end) as YR4,
    max(case when [year] = @yr5 then amount end) as YR5,
    max(case when [year] = @yr6 then amount end) as YR6
FROM #temp
group by field1, field2, field3
order by field1, field2, field3

By the way, maybe sum is more appropriate than max.

Answer 3

i believe there is another way where the query is left as a pivot. and dynamic sql isnt necessary. just cant remember.
but i'll mark viorel's post as acceptable.

Answer 4

Probably you mean a query like this:

select * -- or: field1, field2, field3, YR1, YR2, YR3, YR4, YR5, YR6
from (
    select field1, field2, field3, amount, concat('YR', (@pYear - [year] + 1)) as [year]
    from #temp) t
pivot ( max (amount) FOR [year] IN ( YR1, YR2, YR3, YR4, YR5, YR6  )) p
order by field1, field2, field3