DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
–Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ‘,’,”)
+ QUOTENAME(tablecolumn)
FROM (SELECT DISTINCT tablecolumn FROM tableName) AS Courses
–Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N’SELECT Year, ‘ + @ColumnName + ‘
FROM tableName
PIVOT(SUM(ColumnName)
FOR columnName IN (‘ + @ColumnName + ‘)) AS PVTTable’
–Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery