如何用Oracle SQL实现行列互转？Unpivot&Pivot及其他方案探讨

一、修正你的Unpivot+Pivot代码 #

你的原代码存在几个问题：Pivot子句中年份未加单引号且未指定别名，Unpivot时未显式定义列别名（可能导致后续Pivot逻辑混乱），另外子查询最好明确列出所需字段以避免干扰。

假设你的pivot_test表结构如下：

CREATE TABLE pivot_test (
    yr NUMBER,
    col_1 NUMBER,
    col_2 NUMBER,
    col_1_percentage NUMBER,
    col_2_percentage NUMBER
);

插入示例测试数据：

INSERT INTO pivot_test VALUES (2021, 100, 200, 0.1, 0.2);
INSERT INTO pivot_test VALUES (2022, 150, 250, 0.15, 0.25);

修正后的代码如下：

SELECT
    col,
    "2021",
    "2022"
FROM
    (
        SELECT
            yr,
            col,
            val
        FROM
            pivot_test UNPIVOT (
                val FOR col IN (
                    col_1 AS 'col_1',
                    col_2 AS 'col_2',
                    col_1_percentage AS 'col_1_percentage',
                    col_2_percentage AS 'col_2_percentage'
                )
            )
    ) PIVOT (
        SUM(val) FOR yr IN (
            2021 AS "2021",
            2022 AS "2022"
        )
    );

解释：

• Unpivot时为每个源列指定别名，确保col列的值清晰可读；
• Pivot时为年份值定义列别名，避免生成带引号的默认列名；
• 子查询明确提取yr、col、val三个核心字段，简化后续Pivot逻辑。

二、其他可行实现方案 #

1. 手动列转行（UNION ALL） #

如果你的Oracle版本低于11g（Unpivot是11g引入的特性），可以用UNION ALL手动拼接实现列转行：

SELECT yr, 'col_1' AS col, col_1 AS val FROM pivot_test
UNION ALL
SELECT yr, 'col_2' AS col, col_2 AS val FROM pivot_test
UNION ALL
SELECT yr, 'col_1_percentage' AS col, col_1_percentage AS val FROM pivot_test
UNION ALL
SELECT yr, 'col_2_percentage' AS col, col_2_percentage AS val FROM pivot_test;

这种写法兼容性极强，适合所有Oracle版本，缺点是列数较多时代码会冗长。

2. 手动行转列（CASE WHEN + GROUP BY） #

同理，不用Pivot的话，可通过CASE WHEN配合GROUP BY实现行转列：

SELECT
    col,
    SUM(CASE WHEN yr = 2021 THEN val END) AS "2021",
    SUM(CASE WHEN yr = 2022 THEN val END) AS "2022"
FROM (
    SELECT yr, 'col_1' AS col, col_1 AS val FROM pivot_test
    UNION ALL
    SELECT yr, 'col_2' AS col, col_2 AS val FROM pivot_test
    UNION ALL
    SELECT yr, 'col_1_percentage' AS col, col_1_percentage AS val FROM pivot_test
    UNION ALL
    SELECT yr, 'col_2_percentage' AS col, col_2_percentage AS val FROM pivot_test
) t
GROUP BY col;

该写法逻辑直观，无需依赖新版本特性，适合需要兼容旧系统的场景。

3. XML动态Pivot/Unpivot（处理可变列） #

如果你的源表列数不固定，可使用XML版本的Pivot/Unpivot语法实现动态转换，示例如下：

-- 动态Unpivot
SELECT
    yr,
    EXTRACTVALUE(col_xml, '/row/column[@name="COL"]') AS col,
    EXTRACTVALUE(col_xml, '/row/column[@name="VAL"]') AS val
FROM
    pivot_test
    UNPIVOT XML (val FOR col IN (col_1, col_2, col_1_percentage, col_2_percentage)) t,
    XMLTABLE('/unpivot_set/row' PASSING t.col_xml) xt;

这种方式可以自动适配列的变化，无需修改SQL语句，但需要处理XML解析的逻辑。

三、关键注意事项 #

• 确保Unpivot后的val列数据类型一致，若存在不同类型需提前转换（比如用TO_NUMBER或TO_CHAR统一）；
• Pivot时选择合适的聚合函数：如果每个(yr, col)组合仅有一条数据，用SUM、MAX或MIN效果相同；若有多条数据，需根据业务需求选择聚合逻辑；
• 当列名为数字或特殊字符时，必须用双引号包裹（如"2021"），否则会触发语法错误。

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