如何使用MS-SQL查询实现多行转列（源表转目标表）

方案一：条件聚合（灵活易维护） #

通过分组结合CASE语句与聚合函数，直接提取对应行的列值，是处理固定列数转列的常用方法。

首先为每个Item下的营养记录生成序号，用来匹配目标表的Nut1、GAV1等列：

WITH RankedNutrients AS (
    SELECT 
        ItemId,
        ItemName,
        Nutrient,
        GAV,
        -- 按ItemId分组，给每条营养记录生成递增序号
        ROW_NUMBER() OVER (PARTITION BY ItemId ORDER BY Nutrient) AS NutRank
    FROM 源表名
)
SELECT
    ItemId AS Id,
    ItemName AS Name,
    MAX(CASE WHEN NutRank = 1 THEN REPLACE(Nutrient, ' ', '') END) AS Nut1,
    MAX(CASE WHEN NutRank = 1 THEN GAV END) AS GAV1,
    MAX(CASE WHEN NutRank = 2 THEN REPLACE(Nutrient, ' ', '') END) AS Nut2,
    MAX(CASE WHEN NutRank = 2 THEN GAV END) AS GAV2,
    MAX(CASE WHEN NutRank = 3 THEN REPLACE(Nutrient, ' ', '') END) AS Nut3,
    MAX(CASE WHEN NutRank = 3 THEN GAV END) AS GAV3
FROM RankedNutrients
GROUP BY ItemId, ItemName;

• ROW_NUMBER()用于给每个Item下的营养成分排序，确保列对应关系固定；
• REPLACE(Nutrient, ' ', '')是为了把源表的Vit A转为目标表的VitA，不需要可直接删除；
• 若每个Item的营养成分数量固定，此静态SQL即可满足需求。

方案二：PIVOT操作符 #

适合单字段转列场景，这里需同时处理Nutrient和GAV两个字段，需两次PIVOT结合：

WITH RankedNutrients AS (
    SELECT 
        ItemId,
        ItemName,
        Nutrient,
        GAV,
        ROW_NUMBER() OVER (PARTITION BY ItemId ORDER BY Nutrient) AS NutRank
    FROM 源表名
)
SELECT
    ItemId AS Id,
    ItemName AS Name,
    [1] AS Nut1,
    GAV1 = (SELECT GAV FROM RankedNutrients rn WHERE rn.ItemId = p.ItemId AND rn.NutRank = 1),
    [2] AS Nut2,
    GAV2 = (SELECT GAV FROM RankedNutrients rn WHERE rn.ItemId = p.ItemId AND rn.NutRank = 2),
    [3] AS Nut3,
    GAV3 = (SELECT GAV FROM RankedNutrients rn WHERE rn.ItemId = p.ItemId AND rn.NutRank = 3)
FROM RankedNutrients
PIVOT (
    MAX(Nutrient) FOR NutRank IN ([1], [2], [3])
) AS p;

注意事项 #

• 可修改ROW_NUMBER()中的ORDER BY子句，调整Nut1、Nut2的对应顺序；
• 若每个Item的营养成分数量不固定，需使用动态SQL自动生成列。

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