Oracle基于多字段实现Pivot行转列的SQL查询问题咨询

实现方案 #

你需要的是Oracle下的行转列功能，以下是几种不同场景的实现方式：

1. 静态PIVOT写法（Oracle 11g及以上版本支持，列组合固定场景推荐）

已知identifier_one和identifier_two的所有组合的情况下，直接使用PIVOT语法实现：

SELECT *
FROM (
    SELECT 
        primary_id,
        identifier_one || '_' || identifier_two || '_col' AS col_name,
        value
    FROM 你的实际表名
    -- 在此处添加过滤条件，例如：WHERE primary_id = 10001
)
PIVOT (
    MAX(value) -- varchar类型用MAX/MIN都可以，同一分组下仅对应一个值
    FOR col_name IN (
        '3000_23_col' AS "3000_23_col",
        '3000_24_col' AS "3000_24_col",
        '3001_25_col' AS "3001_25_col",
        '3001_26_col' AS "3001_26_col"
    )
)
ORDER BY primary_id;

2. CASE WHEN传统写法（全版本兼容）

如果你的Oracle版本低于11g不支持PIVOT，可以用分组+CASE WHEN的方式实现，效率和静态PIVOT基本一致：

SELECT 
    primary_id,
    MAX(CASE WHEN identifier_one = 3000 AND identifier_two = 23 THEN value END) AS "3000_23_col",
    MAX(CASE WHEN identifier_one = 3000 AND identifier_two = 24 THEN value END) AS "3000_24_col",
    MAX(CASE WHEN identifier_one = 3001 AND identifier_two = 25 THEN value END) AS "3001_25_col",
    MAX(CASE WHEN identifier_one = 3001 AND identifier_two = 26 THEN value END) AS "3001_26_col"
FROM 你的实际表名
-- 在此处添加过滤条件，例如：WHERE primary_id = 10001
GROUP BY primary_id
ORDER BY primary_id;

3. 动态SQL写法（列组合不固定场景使用）

如果identifier_one和identifier_two的组合会随数据变化，需要动态生成列，可以用PL/SQL动态SQL实现：

DECLARE
    v_sql VARCHAR2(32767);
    v_col_list VARCHAR2(32767);
BEGIN
    -- 动态生成所有需要转换的列配置
    SELECT LISTAGG('''' || col_name || ''' AS "' || col_name || '"', ',') 
           WITHIN GROUP (ORDER BY col_name)
    INTO v_col_list
    FROM (
        SELECT DISTINCT identifier_one || '_' || identifier_two || '_col' AS col_name
        FROM 你的实际表名
        -- 如需限定生成列的范围，可在此处添加过滤条件
    );

    -- 拼接完整查询SQL
    v_sql := '
    SELECT *
    FROM (
        SELECT 
            primary_id,
            identifier_one || ''_'' || identifier_two || ''_col'' AS col_name,
            value
        FROM 你的实际表名
        -- 在此处添加全局过滤条件，例如：WHERE primary_id = 10001
    )
    PIVOT (
        MAX(value)
        FOR col_name IN (' || v_col_list || ')
    )
    ORDER BY primary_id';

    -- 执行语句，调试时可先执行DBMS_OUTPUT.PUT_LINE(v_sql)打印完整SQL校验
    EXECUTE IMMEDIATE v_sql;
END;
/

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