Creating a Pivot Query with Dynamic Column Headers in SQL Server

I needed to create a pivot table in MS SQL Server with dynamic column headers.

“Pivot Table” is an Excel term where you have a field going across the top — ex. months and another field down the side. MS Access database calls this a Cross Tab query

There was no UI for the user to select a date range, so I needed a pure SQL solution that would show all months in my database table.

My data has dates consistently as the last day of the month; that makes grouping the data in columns easier; of course you can write SQL to group the data you need.

Here’s a typical PIVOT statement in SQL:

SELECT *
FROM (
    SELECT last_name, the_month, the_hours
    FROM SampleHours
) AS SourceTable
PIVOT (
    SUM(the_hours)
    FOR the_month IN ([2024-01-01],[2024-02-29],[2024-03-31])
) AS PivotTable;

I’m not a fan of the PIVOT syntax, so I typically use a SELECT statement:

SELECT 
    last_name,
    SUM(CASE WHEN MONTH(the_month) = 1 THEN the_hours ELSE 0 END) AS January,
    SUM(CASE WHEN MONTH(the_month) = 2 THEN the_hours ELSE 0 END) AS February,
    SUM(CASE WHEN MONTH(the_month) = 3 THEN the_hours ELSE 0 END) AS March
FROM 
    SampleHours
WHERE
 YEAR(the_month) = 2024
GROUP BY 
    last_name

I feel like I have more control that way.

I needed to create dynamic column headers (ex. what if my data started in October and went to March of the next year?) Usually I’d use the front end application to generate (retrieve) the date range from the user and I’ll write a loop to dynamically create the SQL.

For this use case, I wanted to use the PIVOT syntax and have dynamic column headers:

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);

-- Get distinct months
WITH ctMonths AS (
    SELECT the_month
    FROM SampleHours
 GROUP BY the_month
)

-- Generate dynamic columns
SELECT @cols = COALESCE(@cols + ', ', '') + QUOTENAME(the_month) 
FROM ctMonths
ORDER BY the_month

-- Put dynamic columns into the SELECT statement
SET @query = 
    'SELECT 
        last_name, ' + @cols + '
    FROM (
        SELECT 
            last_name,
            the_month,
            SUM(the_hours) AS the_hours
        FROM SampleHours
        GROUP BY last_name, the_month
    ) AS pivotData
    PIVOT (
        SUM(the_hours)
        FOR the_month IN (' + @cols + ')
    ) AS pivotTable';

 EXECUTE(@query);

How to preview the SQL

Instead of EXECUTE(@query) you can use PRINT(@query)

Here’s what gets printed out:

SELECT 
        last_name, [2023-02-28], [2023-03-31], [2023-04-30]
    FROM (
        SELECT 
            last_name,
            the_month,
            SUM(the_hours) AS the_hours
        FROM SampleHours
        GROUP BY last_name, the_month
    ) AS pivotData
    PIVOT (
        SUM(the_hours)
        FOR the_month IN ([2023-02-28], [2023-03-31], [2023-04-30]
    ) AS pivotTable

How to remove NULL from the dynamic SQL Pivot

That works but I wanted to have “0” appear instead of NULL.

My first try was this which did not work:

SELECT @cols = COALESCE(@cols + ', ', '') + 'ISNULL(' + QUOTENAME(the_month) + ', 0) AS ' + QUOTENAME(the_month),
FROM ctMonths
ORDER BY the_month

Final Solution

I used a second string to separate out the dynamic columns in the SELECT vs the PIVOT statement

Note you’ll have to declare the extra variable and use a comma to specify two separate variables

DECLARE @colsSelect AS NVARCHAR(MAX), @colsPivot AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);

-- Get distinct months
WITH ctMonths AS (
    SELECT the_month
    FROM SampleHours
 GROUP BY the_month
)

-- Generate dynamic columns
SELECT 
  @colsSelect = COALESCE(@colsSelect + ', ', '') + 'ISNULL(' + QUOTENAME(the_month) + ', 0) AS ' + QUOTENAME(the_month),
  @colsPivot = COALESCE(@colsPivot + ', ', '') + QUOTENAME(the_month)
FROM ctMonths
ORDER BY the_month


-- Put dynamic columns into the SELECT statement
SET @query = 
    'SELECT 
        last_name, ' + @colsSelect + '
    FROM (
        SELECT 
            last_name,
            the_month,
            SUM(the_hours) AS the_hours
        FROM SampleHours
        GROUP BY last_name, the_month
    ) AS pivotData
    PIVOT (
        SUM(the_hours)
        FOR the_month IN (' + @colsPivot  + ')
    ) AS pivotTable';

 EXECUTE(@query);