如何用SQL实现指定格式列转置？解决CASE/PIVOT失效问题

原始数据表 #

期望结果表 #

问题描述 #

需将上述原始表中的TYPE列转置为期望表中的格式，已尝试CASE语句和PIVOT函数但未达到预期效果，尝试的SQL代码如下：

SELECT SKU, CITY, 
case 
when MOV=49 then sum(Total_Imp) end as Type_Imp_Caducidad ,
case
when MOV=48 then sum(Total_Imp) end as Type_Imp_Daño ,
case
when MOV=47 then sum(Total_Imp) end as Type_Imp_Robo
from #movimientos 
where Id_Num_SKU=11466978
group by SKY, CITY, MOV

解决方案 #

方法1：使用CASE语句（修正版） #

原代码的问题在于GROUP BY包含了MOV，导致按MOV分组生成多行结果，且未处理UNI列。修正后按SKU和CITY分组，对每个TYPE对应的UNI和IMP分别聚合：

SELECT 
    SKU, 
    CITY,
    SUM(CASE WHEN TYPE = 'Caducidad' THEN UNI END) AS TYPE_UNI_CADUCIDAD,
    SUM(CASE WHEN TYPE = 'Caducidad' THEN IMP END) AS TYPE_IMP_CADUCIDAD,
    SUM(CASE WHEN TYPE = 'Daño' THEN UNI END) AS TYPE_UNI_DAÑO,
    SUM(CASE WHEN TYPE = 'Daño' THEN IMP END) AS TYPE_IMP_DAÑO,
    SUM(CASE WHEN TYPE = 'Robo' THEN UNI END) AS TYPE_UNI_ROBO,
    SUM(CASE WHEN TYPE = 'Robo' THEN IMP END) AS TYPE_IMP_ROBO
FROM #movimientos 
WHERE Id_Num_SKU = 11466978
GROUP BY SKU, CITY

方法2：使用PIVOT函数 #

由于需要同时转置UNI和IMP两列，可通过构造列名的方式实现：

WITH PivotData AS (
    SELECT 
        SKU, 
        CITY,
        'TYPE_UNI_' + TYPE AS UniColName,
        UNI AS UniValue,
        'TYPE_IMP_' + TYPE AS ImpColName,
        IMP AS ImpValue
    FROM #movimientos 
    WHERE Id_Num_SKU = 11466978
)
SELECT 
    SKU,
    CITY,
    TYPE_UNI_Caducidad,
    TYPE_IMP_Caducidad,
    TYPE_UNI_Daño,
    TYPE_IMP_Daño,
    TYPE_UNI_Robo,
    TYPE_IMP_Robo
FROM PivotData
PIVOT (MAX(UniValue) FOR UniColName IN (TYPE_UNI_Caducidad, TYPE_UNI_Daño, TYPE_UNI_Robo)) AS p1
PIVOT (MAX(ImpValue) FOR ImpColName IN (TYPE_IMP_Caducidad, TYPE_IMP_Daño, TYPE_IMP_Robo)) AS p2
GROUP BY SKU, CITY, TYPE_UNI_Caducidad, TYPE_IMP_Caducidad, TYPE_UNI_Daño, TYPE_IMP_Daño, TYPE_UNI_Robo, TYPE_IMP_Robo

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