How to Dynamically Pivot Data in T-SQL?

Dynamic Pivot in T-SQL

In the field of data processing, dynamic pivot plays a vital role in converting data from row format to column format. This is especially useful when you need to present data in an aggregated and structured way.

Suppose you have a table with data in the following format:

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]

Your goal is to convert this table into the following format:

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

Dynamic Pivot in T-SQL allows you to implement this transformation dynamically without the need for static SQL statements. The following code demonstrates how to accomplish this task:

<code class="language-sql">CREATE TABLE #Table (
        ID INT,
        ColumnName VARCHAR(250),
        Value VARCHAR(250)
)

INSERT INTO #Table SELECT 1,'name','Peter' 
INSERT INTO #Table SELECT 1,'phone','12345678' 
INSERT INTO #Table SELECT 1,'email','[email protected]' 
INSERT INTO #Table SELECT 2,'name','John' 
INSERT INTO #Table SELECT 2,'phone','87654321' 
INSERT INTO #Table SELECT 2,'email','[email protected]' 
INSERT INTO #Table SELECT 3,'name','Sarah' 
INSERT INTO #Table SELECT 3,'phone','55667788' 
INSERT INTO #Table SELECT 3,'email','[email protected]' 

DECLARE @cols NVARCHAR(2000) 
DECLARE @query NVARCHAR(4000) 

SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT 
                                '],[' + t.ColumnName 
                        FROM    #Table AS t 
                        FOR XML PATH('') 
                      ), 1, 2, '') + ']' 

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;' 

EXECUTE(@query)

DROP TABLE #Table</code>

In this code, the STUFF and EXECUTE functions are used to construct dynamic SQL statements. The constructed query dynamically generates PIVOT statements based on different column names in the table. The MAX() aggregate function ensures that the latest value for each column is returned.

The end result is a converted table that meets your needs to present your data in a pivot format. This technique is especially useful when working with larger data sets and complex data structures.

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