解决ORA-00932错误：行转列时CLOB类型不一致及合并NULL行问题

问题场景 #

现有表结构及示例数据如下：

需求是将行转列，把每个name作为独立列，最终得到固定单行结果：

此前尝试的两种方案存在问题：

1. UNION方案触发类型不匹配错误：

SELECT
  value AS a, 
  NULL as b
FROM ex
WHERE name = 'a'

UNION

SELECT
   NULL as a,
   value AS b 
FROM ex
WHERE name = 'b'

错误提示：ORA-00932: inconsistent datatypes: expected - got CLOB

2. CASE方案生成含NULL的多行结果：

SELECT 
    CASE WHEN name = 'a' THEN value ELSE NULL END AS a,
    CASE WHEN name = 'b' THEN value ELSE NULL END AS b
 FROM ex
 WHERE name IN ('a', 'b')

输出结果：

希望通过单次表扫描实现，避免多次连接带来的性能损耗。

解决方案 #

核心思路：聚合函数 + CASE表达式 #

利用MAX/MIN这类聚合函数忽略NULL的特性，将CASE生成的多行结果合并为单行，仅需一次表扫描即可完成。

1. 适用于普通数据类型（NUMBER/VARCHAR2等）

SELECT
    MAX(CASE WHEN name = 'a' THEN value END) AS a,
    MAX(CASE WHEN name = 'b' THEN value END) AS b
FROM ex
WHERE name IN ('a', 'b');

• 原理：CASE表达式为每行生成对应列的有效值或NULL，MAX函数会过滤NULL，提取对应列的唯一有效值，最终合并为单行。

2. 适用于CLOB类型（解决ORA-00932错误）

Oracle默认不支持直接对CLOB使用MAX，可通过两种方式处理：

方式一：转换为VARCHAR2（注意长度限制）

SELECT
    MAX(CASE WHEN name = 'a' THEN TO_CHAR(value) END) AS a,
    MAX(CASE WHEN name = 'b' THEN TO_CHAR(value) END) AS b
FROM ex
WHERE name IN ('a', 'b');

方式二：保留CLOB类型的聚合

使用KEEP(DENSE_RANK FIRST)语法实现CLOB的聚合：

SELECT
    CASE WHEN name = 'a' THEN value END 
        KEEP(DENSE_RANK FIRST ORDER BY CASE WHEN name = 'a' THEN 1 ELSE 2 END) AS a,
    CASE WHEN name = 'b' THEN value END 
        KEEP(DENSE_RANK FIRST ORDER BY CASE WHEN name = 'b' THEN 1 ELSE 2 END) AS b
FROM ex
WHERE name IN ('a', 'b')
GROUP BY 1=1; -- 空分组确保结果为单行

扩展说明 #

• 如需添加更多列（如c），只需在SELECT中追加对应的MAX(CASE WHEN name = 'c' THEN value END) AS c即可；
• 若每个name对应唯一值，MAX和MIN效果一致；若存在多个值，可根据业务需求选择取最大/最小值；
• 该方案仅扫描一次表，性能远优于多次表连接的实现方式。

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