Oracle数据库如何将指定行数据转为列名实现表行列重排

你之前使用PIVOT未达预期，核心是缺少同组内的序号绑定逻辑，具体实现如下：

固定表头实现（col1=1对应的col2值固定为data1/data2/data3时） #

假设你的原表名为t，执行以下SQL即可得到目标结果：

SELECT col1, data1, data2, data3
FROM (
    SELECT 
        col1,
        col2,
        ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2) AS rn
    FROM t
)
PIVOT (
    MAX(col2)
    FOR rn IN (
        1 AS data1,
        2 AS data2,
        3 AS data3
    )
)
WHERE col1 != 1
ORDER BY col1;

动态表头实现（col1=1对应的col2值不固定时） #

如果列名会随数据变化，使用动态SQL实现：

DECLARE
    v_sql VARCHAR2(4000);
    v_pivot_in VARCHAR2(2000);
BEGIN
    -- 动态拼接列名映射
    SELECT LISTAGG(rn || ' AS ' || col2, ',') WITHIN GROUP(ORDER BY rn)
    INTO v_pivot_in
    FROM (
        SELECT col2, ROW_NUMBER() OVER(ORDER BY col2) rn
        FROM t WHERE col1 = 1
    );

    -- 拼接完整SQL
    v_sql := '
        SELECT *
        FROM (
            SELECT col1, col2, ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2) rn
            FROM t
        )
        PIVOT (
            MAX(col2)
            FOR rn IN (' || v_pivot_in || ')
        )
        WHERE col1 != 1
        ORDER BY col1
    ';

    -- 执行SQL，如需返回结果可使用REF_CURSOR接收
    EXECUTE IMMEDIATE v_sql;
END;
/

实现逻辑说明 #

• 先用ROW_NUMBER()窗口函数给同一col1分组下的col2值按顺序生成序号，保证每个组的第N个值和表头的第N个列对应
• PIVOT聚合时用MAX/MIN都可，因为每个col1+rn组合下只会有一条数据
• 最终过滤掉col1=1的原表头行，得到你需要的转换结果

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