如何使用SQL Server的UNPIVOT实现全表转置？

问题描述 #

原表结构：

期望输出：

尝试的错误代码：

SELECT [ManufacturerID], [Manufacturer], [Logo] 
FROM ( 
    SELECT [Column1] , [Column2],[Column3],[Column4],[Column5] 
    FROM [TestFormation].[dbo].[IN_Manufacturer] 
) unpivot ([ManufacturerID] FOR Name_ToDrop1 IN ([Column2],[Column3],[Column4],[Column5] )) AS p1 
unpivot ([Manufacturer] FOR Name_ToDrop2 IN ([Column2],[Column3],[Column4],[Column5] )) AS p2 
unpivot (Logo] FOR Name_ToDrop3 IN ([Column2],[Column3],[Column4],[Column5] )) AS p

问题分析 #

多次UNPIVOT的写法逻辑错误，每次UNPIVOT会独立展开列，导致结果是笛卡尔积而非对应匹配的行。正确思路是先将原表的编号列（1、2、3、4）转换为行，同时保留Manufacturer和Logo的对应值，再重新组织成目标结构。

正确解法 #

方法1：使用CROSS APPLY展开列 #

直观提取每个编号列对应的属性值，再聚合得到目标行：

SELECT 
    CAST(p.ManufacturerID AS VARCHAR(10)) AS ManufacturerID,
    MAX(CASE WHEN t.ManufacturerID = 'Manufacturer' THEN p.Value END) AS Manufacturer,
    MAX(CASE WHEN t.ManufacturerID = 'Logo' THEN p.Value END) AS Logo
FROM [TestFormation].[dbo].[IN_Manufacturer] t
CROSS APPLY (
    VALUES 
        ('1', [1]),
        ('2', [2]),
        ('3', [3]),
        ('4', [4])
) p(ManufacturerID, Value)
GROUP BY p.ManufacturerID
ORDER BY p.ManufacturerID;

方法2：先UNPIVOT再PIVOT #

先将原表转成键值对形式，再通过PIVOT将属性转为列：

SELECT 
    ManufacturerID,
    Manufacturer,
    Logo
FROM (
    SELECT 
        t.ManufacturerID AS Attribute,
        p.ManufacturerID,
        p.Value
    FROM [TestFormation].[dbo].[IN_Manufacturer] t
    UNPIVOT (
        Value FOR ManufacturerID IN ([1], [2], [3], [4])
    ) p
) src
PIVOT (
    MAX(Value) FOR Attribute IN (Manufacturer, Logo)
) piv
ORDER BY ManufacturerID;

说明 #

• 方法1适合列数较少的场景，通过CROSS APPLY生成对应行，再用CASE和GROUP BY聚合属性值。
• 方法2逻辑更通用，先UNPIVOT转成行结构，再PIVOT转回目标列结构，适配属性或列数较多的情况。

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