How to use variables as pivot columns on SQL Server

Question

I am having an issue making a table with pivot columns more dynamic on MS SQL Server.

I have a query that retrieves data from a five week period of the current year and a five week period of the previous year, so the columns CUR_YR and PREV_YR are the ones that come from the pivot clause. This is my query:

DECLARE @TODAY DATE
SET @TODAY= CAST(GETDATE() AS DATE)
DECLARE @PREV_YR INT, @CUR_YR INT
SET @CUR_YR = YEAR(@TODAY)
SET @PREV_YR = @CUR_YR-1

SELECT * FROM (
    SELECT BUS, DET, [STR], COALESCE(@PREV_YR,0) AS PREV_YR, COALESCE(@CUR_YR,0) AS CUR_YR FROM (
        SELECT YR,BUS, DET,[STR],count(ORD_ID_SE) as ORDS FROM (<SUBQUERY>) )AS T
PIVOT(
    SUM(ORDS)
    FOR YR IN (
            @PREV_YR, @CUR_YR)
) AS pivot_table
)AS F

When ever I manually set the pivot columns inside the FOR YR IN() clause as 2020 and 2021, the query works, but since I need these columns to be dynamic (aka. make them change as time goes on, so next year they are 2021 and 2022) I use the variables @PREV_YR and @CUR_YR as shown in the code. However, this results in the error "Incorrect syntax near '@PREV_YR'.".

Is there a work around for this? Maybe a different way to feed the two years inside the IN() clause.

Thank you for your help.

Answer 1

This is a known limitation of the PIVOT functionality. You must have a pre-defined set of columns within your IN list. They can not change dynamically. So in order to do this you would need to utilize dynamic SQL to change your result set based on your input values.

There are many guides on how to do this out there, but I will give an example of how you could apply it to your script (with some minor reformatting added). I did correct two issues in order to run it. 1.Add alias to your subquery subquery_alias 2. Add GROUP BY clause to your aggregate query

DECLARE @TODAY DATE = CAST(GETDATE() AS DATE); 
DECLARE @CUR_YR INT = YEAR(@TODAY); 
DECLARE @PREV_YR INT = @CUR_YR-1; 
DECLARE @Sql nvarchar(max) =
N'SELECT    
    * 
FROM 
    (SELECT 
        BUS
        ,DET
        ,[STR]
        ,COALESCE(' + QUOTENAME(CAST(@PREV_YR AS nvarchar(10))) + N',0) AS PREV_YR
        ,COALESCE(' + QUOTENAME(CAST(@CUR_YR AS nvarchar(10))) + N',0) AS CUR_YR 
    FROM 
        (SELECT 
            YR
            ,BUS
            ,DET
            ,[STR]
            ,COUNT(ORD_ID_SE) AS ORDS 
        FROM 
            (<SUBQUERY>) AS subquery_alias
        GROUP BY 
            YR
            ,BUS
            ,DET
            ,[STR]) AS T
PIVOT(SUM(ORDS) FOR YR IN (' + QUOTENAME(CAST(@PREV_YR AS nvarchar(10))) + N',' + QUOTENAME(CAST(@CUR_YR AS nvarchar(10))) + N')) AS pivot_table) AS F'; 

EXEC sys.sp_executesql @Sql; 

That being said, in this case you are re-aliasing the output of your pivot tables to be PREV_YR and CUR_YR respectively. So you really don't need dynamic column names and if you are only ever going to use two different variables in this manner then it makes more sense to pivot it with a SUM(CASE WHEN....END) method. Like so:

DECLARE @TODAY DATE = CAST(GETDATE() AS DATE); 
DECLARE @CUR_YR INT = YEAR(@TODAY); 
DECLARE @PREV_YR INT = @CUR_YR-1; 
SELECT  
    BUS
    ,DET
    ,[STR]
    ,SUM(CASE WHEN YR = @PREV_YR THEN 1 ELSE 0 END) AS PREV_YR
    ,SUM(CASE WHEN YR = @CUR_YR THEN 1 ELSE 0 END) AS CUR_YR
FROM 
    (<SUBQUERY>) AS subquery_alias
GROUP BY 
    BUS
    ,DET
    ,[STR];

Doing this you can completely avoid dynamic SQL and a more complicated pivot query.