Using Dynamic SQL to create a Pivot Table

I recently came across a situation whereby I needed to create a SQL Pivot Table based on every day between now and 6 weeks time. Those of you who have used a pivot table within SQL will know that you need to provide the names for each group of data that you wish to pivot, that then become the column names. I started researching how to dynamically create the columns that were needed for the table and came across the following solution.

Firstly, I started by declaring and setting some of the required variables for the query, the first and second being the start and end date for the query. Once the query is run, the third variable will contain the dates for every day required and will provide SQL with the column names.

DECLARE @startdate DATETIME, 
@enddate DATETIME, 
@QueryCol NVARCHAR(MAX)

SET @startdt = GETDATE()
SET @enddt = DATEADD(dd, 56, GETDATE())

I then queried the database using a simple SELECT INTO query, this provided the baseline of data that I need to work with later.

SELECT  EngineerName,
        WorkDate
INTO    #WorkloadRaw
FROM    Case
WHERE   WorkDate > @startdate
        AND WorkDate < @enddate
        AND Status = 1

The code below runs everything inside on the BEGIN and END until the condition becomes true, which is the StartDate is less than or equal to the EndDate. Every time SQL iterates through the code, it adds 1 day onto the StartDate variable. The SET portion of the query is where the column names for the PIVOT table are created. The following part of the query:

SET @QueryCol = ISNULL(@QueryCol, '')

checks if the @QueryCol variable is null (blank) and if it is, then it sets it as an empty string. The next part of the query:

QUOTENAME(CONVERT(NVARCHAR(20), @startdate, 111)) + _
( CASE WHEN @startdate <> @enddate THEN ',' ELSE '' END )

uses the QUOTENAME keyword to build a comma delimited string.

WHILE @startdate <= @enddate 
    BEGIN
        SET @QueryCol = ISNULL(@QueryCol, '')
            + QUOTENAME(CONVERT(NVARCHAR(20), @startdate, 111))
            + ( CASE WHEN @startdate <> @enddate THEN ','
                     ELSE ''
                END )
        SET @startdate = @startdate + 1
    END

The next part of the query first checks that the value of the @QueryCol variable isn’t empty. If the variable is not empty, it will proceed to set the @QueryCol variable to the following string value:

'SELECT * from #WorkloadRaw PIVOT (COUNT(WorkDate) for WorkDate in (' + @QueryCol + ')) _
as NoOfJobsPerDay'

as you can see it also adds in the results of the previous part of the query. The query will then execute the string value as a SQL query which passed into the EXEC keyword.

IF ( ISNULL(@QueryCol, '') <> '' ) 
    BEGIN
        SET @QueryCol = 'SELECT * from #WorkloadRaw
                    PIVOT (COUNT(WorkDate) for 
                    WorkDate in (' + @QueryCol
            + ')) as NoOfJobsPerDay'

        EXEC (@QueryCol)
    END

Finally, you need to drop the temporary table using the code below:

DROP TABLE #WorkloadRaw

I hope that this post will help others and below is a full version of all the snippets above.

DECLARE @startdate DATETIME, 
@enddate DATETIME, 
@QueryCol NVARCHAR(MAX)

SET @startdt = GETDATE()
SET @enddt = DATEADD(dd, 56, GETDATE())

SELECT  EngineerName,
        WorkDate
INTO    #WorkloadRaw
FROM    Case
WHERE   WorkDate > @startdate
        AND WorkDate < @enddate
        AND Status = 1  

WHILE @startdate <= @enddate 
    BEGIN
        SET @QueryCol = ISNULL(@QueryCol, '')
            + QUOTENAME(CONVERT(NVARCHAR(20), @startdate, 111))
            + ( CASE WHEN @startdate <> @enddate THEN ','
                     ELSE ''
                END )
        SET @startdate = @startdate + 1
    END

IF ( ISNULL(@QueryCol, '') <> '' ) 
    BEGIN
        SET @QueryCol = 'SELECT * from #WorkloadRaw
                    PIVOT (COUNT(WorkDate) for 
                    WorkDate in (' + @QueryCol
            + ')) as NoOfJobsPerDay'

        EXEC (@QueryCol)
    END

DROP TABLE #WorkloadRaw