基于多依赖列实现Pivot行转列的SQL查询问题咨询

原有写法错误说明 #

• PIVOT语法仅支持单次聚合一个指标，原写法sum(Min, Value)属于非法语法，同时聚合两个字段会直接报错
• 字段拼接逻辑不符合预期列名规则，原concat(coalesce(Location, Year, Min, 'NULL'),'_Min_Value_Year')生成的字段名和需要的<位置>_<指标>_<年份>格式完全不匹配
• 源查询赋值语法错误，Value = Min, Value, Year属于非法赋值，不能同时给单个字段赋值多个值
• PIVOT的IN子句包含了不存在的字段[NULL_Min]、[NULL_Value]，同时和源数据生成的Item值无法对应，会返回全空结果

正确实现方案 #

方案1：条件聚合（全数据库兼容，推荐使用） #

该方案兼容MySQL、PostgreSQL、SQL Server等所有主流数据库，逻辑清晰易调试：

SELECT 
    Super_Location,
    MAX(CASE WHEN Location = 'Primary' AND Year = 2020 THEN Min END) AS Primary_Min_2020,
    MAX(CASE WHEN Location = 'Primary' AND Year = 2019 THEN Min END) AS Primary_Min_2019,
    MAX(CASE WHEN Location = 'Secondary' AND Year = 2020 THEN Min END) AS Secondary_Min_2020,
    MAX(CASE WHEN Location = 'Secondary' AND Year = 2019 THEN Min END) AS Secondary_Min_2019,
    MAX(CASE WHEN Location = 'Primary' AND Year = 2020 THEN Value END) AS Primary_Division_2020,
    MAX(CASE WHEN Location = 'Primary' AND Year = 2019 THEN Value END) AS Primary_Division_2019,
    MAX(CASE WHEN Location = 'Secondary' AND Year = 2020 THEN Value END) AS Secondary_Division_2020,
    MAX(CASE WHEN Location = 'Secondary' AND Year = 2019 THEN Value END) AS Secondary_Division_2019
FROM TestTable
GROUP BY Super_Location

逻辑说明：按Super_Location分组，通过CASE语句匹配Location、Year维度，分别提取Min和Value（对应输出的Division指标）的值，无匹配项自动返回NULL，完全符合预期输出要求。

方案2：UNPIVOT + PIVOT 语法实现（适配SQL Server、Oracle等支持PIVOT语法的数据库） #

SELECT *
FROM (
    SELECT 
        Super_Location,
        CONCAT(Location, '_', Metric, '_', Year) AS Item,
        Val
    FROM TestTable
    -- 先把Min、Value两个指标拆为多行，Value别名对应输出的Division
    UNPIVOT (
        Val FOR Metric IN (Min, [Value] AS Division)
    ) upvt
) src
PIVOT (
    MAX(Val)
    FOR Item IN (
        [Primary_Min_2020],[Primary_Min_2019],
        [Secondary_Min_2020],[Secondary_Min_2019],
        [Primary_Division_2020],[Primary_Division_2019],
        [Secondary_Division_2020],[Secondary_Division_2019]
    )
) pvt

补充说明：如果Location、Year的取值不固定，可使用动态SQL拼接CASE语句或PIVOT的IN子句，实现无需手动维护列名的动态行转列。

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