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