How to Dynamically Pivot Data in SQL Server with Dynamic Columns?

Dynamic SQL Server PIVOT for Dynamic Columns

You have a dataset with multiple columns ('Name1' and 'Name2') and a value column ('Value'). You want to transform this data into a pivoted format where 'Name1' becomes the column headers and the values are aggregated by 'Name2'.

Solution with Dynamic SQL (SQL Server 2005 or Later)

Since SQL Server 2005 allows for dynamic SQL, you can solve this problem by dynamically assembling the PIVOT query. Here's the code:

DECLARE @cols VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)

SELECT  @cols = STUFF(( SELECT distinct  ',' + QuoteName([Name1])
                        FROM myTable FOR XML PATH('') ), 1, 1, '') 


SET @sqlquery = 'SELECT * FROM
      (SELECT Name2, Name1, Value
       FROM myTable ) base
       PIVOT (Sum(Value) FOR [Name1]
       IN (' + @cols + ')) AS finalpivot'

EXECUTE ( @sqlquery )

This query will work dynamically no matter how many unique 'Name1' values you have. It assigns the collected 'Name1' values to the variable @cols, which is then used to dynamically construct the PIVOT clause in the @sqlquery variable. The final query is then executed, producing your desired output.

Additional Resources:

• [Pivot data in T-SQL](https://www.mssqltips.com/sqlservertip/1355/pivot-data-in-tsql/)
• [How do I build a summary by joining to a single table with SQL Server?](https://stackoverflow.com/questions/260660/how-do-i-build-a-summary-by-joining-to-a-single-table-with-sql-server)
• [Dynamic PIVOT with dynamic columns](https://stackoverflow.com/questions/8248059/pivot-with-dynamic-columns-in-sql-server-2005)

The above is the detailed content of How to Dynamically Pivot Data in SQL Server with Dynamic Columns?. For more information, please follow other related articles on the PHP Chinese website!