Dynamic PIVOT In Sql Server

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 value, and no need to worry if the 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 column’s 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 gets 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 dynamic, basically, we have to remove the hardcoded PIVOT column names specified in the PIVOT operators PIVOT columns IN clause. The 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 + ',','') 
       + QUOTENAME(Course)
FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses
 
--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT Year, ' + @ColumnName + '
    FROM #CourseSales
    PIVOT(SUM(Earning) 
          FOR Course IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery