How to Achieve Dynamic Pivoting of Tabular Data in T-SQL?

Dynamic Pivot in T-SQL

Converting tabular data into a more concise format is critical for data analysis and report generation. One commonly used technique is pivoting, which involves converting rows into columns. In this example, our goal is to pivot data from a table with a structure similar to:

ItemID	ColumnName	Value
1	name	Peter
1	phone	12345678
1	email	[email protected]
2	name	John
2	phone	87654321
2	email	[email protected]
3	name	Sarah
3	phone	55667788
3	email	[email protected]

Convert to a more user-friendly structure:

ItemID	name	phone	email
1	Peter	12345678	[email protected]
2	John	87654321	[email protected]
3	Sarah	55667788	[email protected]

To achieve this in T-SQL using dynamic pivots, follow these steps:

1. Create a dynamic column list:

   <code class="language-sql"> DECLARE @cols NVARCHAR(2000)
    SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                     '],[' + t.ColumnName
                             FROM    #Table AS t
                             FOR XML PATH('')
                           ), 1, 2, '') + ']' </code>

2. Build a dynamic query:

   <code class="language-sql"> DECLARE @query NVARCHAR(4000) 
    SET @query = N'SELECT ID,'+ @cols +' FROM 
    (SELECT t1.ID,t1.ColumnName , t1.Value FROM #Table AS t1) p 
    PIVOT (MAX([Value]) FOR ColumnName IN ( '+ @cols +' )) 
    AS pvt;' </code>

3. Execute query:

   <code class="language-sql"> EXECUTE(@query)</code>

The generated query will dynamically pivot the data, creating a more compact and easy-to-read table structure.

This approach provides a flexible mechanism for pivoting data, especially if column names are dynamic or may change. It allows efficient and automated data transformation, making it a powerful tool for a variety of data analysis and reporting applications.

The above is the detailed content of How to Achieve Dynamic Pivoting of Tabular Data in T-SQL?. For more information, please follow other related articles on the PHP Chinese website!