In this Article will present how we can write a Dynamic PIVOT query with an example, where we don’t need to mention the PIVOT columns each unique values and no need to worry if PIVOT column gets extra unique values after the initial query.
In the Previous Post PIVOT and UNPIVOT in Sql Server explained how PIVOT relational operator can be used to transform columns distinct values as Columns in the result set by mentioning all the distinct column values in the PIVOT operators PIVOT columns IN clause.
This type of PIVOT query is called Static PIVOT query, because if the PIVOT column in the source table get’s extra unique values after the initial query then that will not reflect in the PIVOT query result unless it is mentioned in the PIVOT Columns IN clause. Static PIVOT queries are fine as long as we know that the PIVOT column values never change, for instance if PIVOT column values are MONTH or Day of the Week or hour of the day etc.
To make the above Static PIVOT query to dynamic, basically we have to remove the hardcoded PIVOT column names specified in the PIVOT operators PIVOT columns IN clause. Below query demonstrates this.
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N'SELECT Year, ' + @ColumnName + '
FOR Course IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery