SQL - Dynamic Pivot in SQL Server

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




--OR




EXEC(@DynamicPivotQuery)

Result: