Dynamic Pivot Column Data in SQL Server | Rows convert to column in SQL Server

3/3/2022 11:25:01 AM

  • Declare two variables like below
--Declare Variable 

DECLARE @Pivot_Column [nvarchar](max); 

DECLARE @Query [nvarchar](max); 
  • Select comma-separated dynamic column.
 --Select Pivot Column

SELECT @Pivot_Column= COALESCE(@Pivot_Column+',','')+ QUOTENAME(BreakupName) FROM 

                    (SELECT DISTINCT SB.BreakupName FROM [Pay].[SalaryDetails] SD

                    JOIN [Pay].[SalaryBreakUp] SB ON SD.BreakupID=SB.BreakupID)Tab 
  • Create dynamic query and use selected @Pivot_Column in selection.
--Create Dynamic Query

SELECT @Query='SELECT EmployeeID, '+@Pivot_Column+' FROM   

                    (SELECT SD.EmployeeID, SB.BreakupName , SD.BreakupAmount FROM [Pay].[SalaryDetails] SD

                    JOIN [Pay].[SalaryBreakUp] SB ON SD.BreakupID=SB.BreakupID )Tab1 



                    SUM(BreakupAmount) FOR [BreakupName] IN ('+@Pivot_Column+')) AS Tab2 

                    ORDER BY Tab2.EmployeeID'
  • Execute query
 --Execute Query

 EXEC  sp_executesql  @Query


About Teacher

Reza Karim

Software Engineer

More about him