SQL多行转列问题：基于Reason与Weight字段的PIVOT操作实现咨询

原查询错误原因 #

• 聚合逻辑错误：PIVOT的聚合函数应该作用于要展开的Weight字段，而非分组字段Year
• 转换维度错误：FOR关键字后应指定要枚举的维度字段Reason，而非Climate，IN子句内需列出Reason的所有预设取值

方案1：通用CASE WHEN + GROUP BY 实现（兼容所有SQL方言） #

这种写法不需要适配不同数据库的PIVOT语法差异，逻辑更可控：

SELECT 
    Location,
    Year,
    Climate,
    MAX(CASE WHEN Reason = 'Natural' THEN Weight END) AS Natural_Weight,
    MAX(CASE WHEN Reason = 'Emissions' THEN Weight END) AS Emissions_Weight,
    MAX(CASE WHEN Reason = 'Humans' THEN Weight END) AS Humans_Weight,
    MAX(CASE WHEN Reason IS NULL THEN Weight END) AS NULL_Weight
FROM Test
GROUP BY Location, Year, Climate
ORDER BY Location DESC, Year DESC, Climate DESC;

这里使用MAX/SUM均可，只要保证同一(Location,Year,Climate)分组下同一个Reason只有一条记录，聚合结果就和原值完全一致。

方案2：PIVOT 语法实现 #

需要先预处理Reason的空值，避免空值无法被PIVOT匹配，不同数据库空值处理函数可替换：MySQL用IFNULL，PostgreSQL/Oracle用COALESCE即可：

SELECT 
    Location,
    Year,
    Climate,
    Natural AS Natural_Weight,
    Emissions AS Emissions_Weight,
    Humans AS Humans_Weight,
    NULL_VAL AS NULL_Weight
FROM (
    -- 预处理空值为可枚举的字符串标识
    SELECT 
        Location,
        Year,
        Climate,
        Weight,
        IFNULL(Reason, 'NULL_VAL') AS Reason
    FROM Test
) src
PIVOT (
    MAX(Weight) -- 对Weight做聚合
    FOR Reason IN (
        'Natural' AS Natural,
        'Emissions' AS Emissions,
        'Humans' AS Humans,
        'NULL_VAL' AS NULL_VAL
    )
) pvt
ORDER BY Location DESC, Year DESC, Climate DESC;

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