How to Dynamically Pivot Data in T-SQL Using a Dynamic Pivot?

Transform data in T-SQL using dynamic pivots

Sometimes it is necessary to convert data from wide format to a more structured tabular format. Consider a situation where a table contains multiple columns of different data types and values, and the data needs to be pivoted dynamically to obtain a result set that provides a different row for each ID and corresponding column value.

One way to achieve this conversion is to use T-SQL's dynamic pivot feature. The following example demonstrates how to dynamically pivot data using T-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 
                        -- 按 ID 排序以确保列顺序一致
                        ORDER BY '],[' + CAST(t.ID AS VARCHAR(10)) 
                        FOR XML PATH('') 
                      ), 1, 2, '') + ']' 

SELECT  @cols;

-- 动态构建 PIVOT 查询
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;'; 

-- 执行动态 PIVOT 查询
EXECUTE(@query);

-- 删除临时表
DROP TABLE #Table;

The result of executing the above script will be a pivot table with a distinct row for each ID and corresponding column value.

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