question

db042190-2983 avatar image
0 Votes"
db042190-2983 asked Viorel-1 answered

making a pivot more flexible without using dynamic sql

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;  
sql-server-transact-sql
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.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

Hi @db042190-2983

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

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.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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.



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.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered Viorel-1 converted comment to answer

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.

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.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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


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.