SQL Server 2014中使用动态SQL实现多列数据转置

需求概述 #

创建包含Columnname1、Value1、Columnname2、Value2等字段的临时表#TempData，其中除Columnname1外其他列可扩展；需将数据转置为宽表格式，且列名不固定，必须动态实现。

临时表创建与示例数据 #

-- 创建临时表（支持扩展ColumnnameN/ValueN列）
CREATE TABLE #TempData (
    Columnname1 VARCHAR(50),
    Value1 VARCHAR(100),
    Columnname2 VARCHAR(50),
    Value2 VARCHAR(100),
    Columnname3 VARCHAR(50),
    Value3 VARCHAR(100)
)

-- 插入示例数据
INSERT INTO #TempData
VALUES 
('ID', '1001', 'Name', 'John Doe', 'Department', 'IT'),
('ID', '1002', 'Name', 'Jane Smith', 'Department', 'HR'),
('ID', '1003', 'Name', 'Bob Brown', 'Department', 'Finance')

用户尝试的分批次转置代码（存在缺陷） #

-- 第一次转置Columnname1/Value1
SELECT * INTO #Temp1 FROM (
    SELECT Columnname1, Value1 FROM #TempData
) AS P
PIVOT (MAX(Value1) FOR Columnname1 IN ([ID])) AS Pvt1

-- 第二次转置Columnname2/Value2
SELECT * INTO #Temp2 FROM (
    SELECT Columnname2, Value2 FROM #TempData
) AS P
PIVOT (MAX(Value2) FOR Columnname2 IN ([Name])) AS Pvt2

-- 第三次转置Columnname3/Value3
SELECT * INTO #Temp3 FROM (
    SELECT Columnname3, Value3 FROM #TempData
) AS P
PIVOT (MAX(Value3) FOR Columnname3 IN ([Department])) AS Pvt3

-- 尝试关联临时表（无有效关联键，实际执行会出错）
SELECT t1.ID, t2.Name, t3.Department 
FROM #Temp1 t1
JOIN #Temp2 t2 ON t1.ID = t2.ID
JOIN #Temp3 t3 ON t1.ID = t3.ID

兼容SQL Server 2014的完整动态SQL解决方案 #

以下代码可自动识别所有ColumnnameN/ValueN列，一次性完成转置，无需手动指定列名：

DECLARE @unpivotCols1 AS NVARCHAR(MAX),
        @unpivotCols2 AS NVARCHAR(MAX),
        @pivotCols AS NVARCHAR(MAX),
        @dynamicQuery AS NVARCHAR(MAX)

-- 1. 动态收集所有Columnname列（用于Unpivot）
SELECT @unpivotCols1 = STUFF((
    SELECT ', ' + QUOTENAME(COLUMN_NAME)
    FROM tempdb.sys.columns 
    WHERE OBJECT_ID = OBJECT_ID('tempdb..#TempData')
      AND COLUMN_NAME LIKE 'Columnname%'
    ORDER BY COLUMN_NAME
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

-- 2. 动态收集所有Value列（用于Unpivot）
SELECT @unpivotCols2 = STUFF((
    SELECT ', ' + QUOTENAME(COLUMN_NAME)
    FROM tempdb.sys.columns 
    WHERE OBJECT_ID = OBJECT_ID('tempdb..#TempData')
      AND COLUMN_NAME LIKE 'Value%'
    ORDER BY COLUMN_NAME
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

-- 3. 动态收集转置后的目标列名（即ColumnnameN字段的值）
SELECT @pivotCols = STUFF((
    SELECT DISTINCT ', ' + QUOTENAME(td.COLUMN_NAME)
    FROM #TempData td
    CROSS APPLY (
        SELECT COLUMN_NAME 
        FROM tempdb.sys.columns 
        WHERE OBJECT_ID = OBJECT_ID('tempdb..#TempData')
          AND COLUMN_NAME LIKE 'Columnname%'
    ) AS cn
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

-- 4. 构建动态转置查询
SET @dynamicQuery = N'
WITH UnpivotedData AS (
    SELECT 
        RowID,
        ColumnName,
        Value
    FROM (
        SELECT 
            -- 生成行ID，确保同一原始行的Columnname/Value对应
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowID,
            ' + @unpivotCols1 + N',
            ' + @unpivotCols2 + N'
        FROM #TempData
    ) AS SourceData
    -- 逆转所有Columnname列为行
    UNPIVOT (
        ColumnName FOR ColumnCols IN (' + @unpivotCols1 + N')
    ) AS UnpivotColumns
    -- 逆转所有Value列为行
    UNPIVOT (
        Value FOR ValueCols IN (' + @unpivotCols2 + N')
    ) AS UnpivotValues
    -- 匹配ColumnnameN和ValueN的序号（确保Columnname1对应Value1，以此类推）
    WHERE RIGHT(ColumnCols, LEN(ColumnCols)-10) = RIGHT(ValueCols, LEN(ValueCols)-5)
)
-- 将行转置为宽表
SELECT ' + @pivotCols + N'
FROM UnpivotedData
PIVOT (
    MAX(Value) FOR ColumnName IN (' + @pivotCols + N')
) AS PivotedResult'

-- 执行动态SQL
EXEC sp_executesql @dynamicQuery

执行结果 #

执行上述代码后，将得到如下宽表格式的结果：

内容的提问来源于stack exchange，提问作者subbu2464