Oracle中PIVOT行转列与UNPIVOT列转行该如何正确使用？

在Oracle数据库开发中，行转列和列转行是报表与数据清洗中绕不开的操作。行转列通常用来把某一列上的多个取值转换成多个字段，让每一行显示更完整；列转行则相反，把原本分散在多列的数据合并到一列，方便做聚合或关联。Oracle从11g开始提供PIVOT和UNPIVOT关键字，让这些操作不再只能依赖CASE WHEN或UNION ALL。

一、PIVOT行转列的核心机制

PIVOT的作用是把某个列中的不同取值转换为多个列。它的语法结构包含三个关键部分：聚合函数、FOR子句和IN子句。聚合函数决定新列中填充什么值，FOR子句指定要转置的列，IN子句列出需要转换成列名的所有值。一个典型的查询如下：

SELECT *
FROM (
  SELECT product, quarter, sales_amount
  FROM sales_detail
)
PIVOT (
  SUM(sales_amount)
  FOR quarter IN ('Q1' AS q1, 'Q2' AS q2, 'Q3' AS q3, 'Q4' AS q4)
);

内层子查询只保留需要参与旋转的列。PIVOT会隐式按子查询中除聚合列和FOR列以外的其他列进行分组，因此如果子查询多出一个不需要的字段，结果行数会成倍增加。上面的例子按product分组，把quarter的不同取值Q1、Q2、Q3、Q4转换为四个列，每个列的值是对应季度的销售额总和。

PIVOT适用于固定取值集合的场景，比如月份、季度、地区、状态等。如果取值集合会随时间变化，静态IN列表需要不断维护，这时要考虑动态SQL或改用报表工具。对于需要同时转换多个聚合指标的场合，可以在PIVOT中写多个聚合函数，Oracle会生成组合列，例如同时统计销售额和订单数时，IN列表会生成Q1_SALES、Q1_ORDERS这样的列。

PIVOT内部本质上是在执行分组聚合，因此聚合字段若包含NULL，转换后的列会是NULL；如果某行没有对应季度数据，SUM结果为空。可以通过NVL函数包裹聚合表达式处理空值，例如NVL(SUM(sales_amount), 0)。另外PIVOT要求IN子句中的值类型与FOR列一致，字符串类型大小写敏感，务必保证数据源的季度值精确匹配，否则该取值不会出现在结果列中。

二、UNPIVOT列转行的语法与典型场景

UNPIVOT与PIVOT相反，它把多个列压缩成两列：一列保存原列名，一列保存原列值。语法中需要指定一个值列和一个列名列，然后在IN子句中列出一组原宽表列。示例：

SELECT product, quarter, sales_amount
FROM quarterly_sales
UNPIVOT (
  sales_amount
  FOR quarter IN (q1, q2, q3, q4)
);

这里原来的quarterly_sales表中有q1、q2、q3、q4四列，UNPIVOT把它们合并成quarter和sales_amount两列。转换后每一行由原一行的多列拆成多行，如果原值为NULL，默认会被剔除，除非使用INCLUDE NULLS。列转行在数据规范化、把Excel样式的宽表导入关系表、以及将多个指标列转成行便于统计分析时非常实用。

相比传统UNION ALL写法，UNPIVOT的优势是代码更简洁，且只扫描一次表。不过它要求被转换的列类型一致或可以隐式转换，否则会报错。如果宽表中同时存在数值型和字符串型指标，需要先做类型转换，或者在UNPIVOT前用TO_CHAR将数值列统一为字符串。另外UNPIVOT生成的列名列为原列名，默认大写，若原列名是小写且带引号创建，Oracle会按原始大小写保留，这种建表方式较少见，但排查数据时需要注意。

三、PIVOT与UNPIVOT的组合使用

很多场景需要先把列转行，经过处理后再行转列。例如一张表以月份作为列：jan_sales、feb_sales、mar_sales，现在要做区域汇总分析，先把三个月数据UNPIVOT成month与sales两列，关联维度表补全地区，再用PIVOT按地区生成汇总行。组合写法如下：

SELECT *
FROM (
  SELECT region, month, sales_amount
  FROM monthly_region_sales
  UNPIVOT (
    sales_amount
    FOR month IN (jan_sales, feb_sales, mar_sales)
  )
)
PIVOT (
  SUM(sales_amount)
  FOR region IN ('East' AS east, 'West' AS west, 'North' AS north)
);

先拆后合能灵活处理原始表结构不合理的数据，但要注意执行计划和性能。PIVOT与UNPIVOT都是逻辑上的转置操作，Oracle底层会转换成分组查询或UNION ALL，不会真正创建新的物理存储。随着数据量增大，可以先在子查询里过滤和聚合，再转置，减少旋转处理的行数。也要避免把PIVOT和UNPIVOT用在递归或嵌套过深的视图里，优化器可能无法很好地展开谓词。

一个常见错误是将PIVOT结果直接作为另一个PIVOT的输入，又不限制列名，导致列名不确定。建议每一层都使用子查询明确输出列，避免后续引用时出现找不到列的问题。如果列集合固定，用CASE WHEN虽然冗长，但更容易控制执行计划；如果列集合动态，则用PL/SQL拼动态SQL的方式，或把行列转换交给前端展示层处理。

四、替代写法与性能对比

在PIVOT出现之前，行转列通常使用CASE WHEN配合GROUP BY。例如同样的季度销售转置：

SELECT product,
       SUM(CASE WHEN quarter = 'Q1' THEN sales_amount ELSE 0 END) AS q1,
       SUM(CASE WHEN quarter = 'Q2' THEN sales_amount ELSE 0 END) AS q2,
       SUM(CASE WHEN quarter = 'Q3' THEN sales_amount ELSE 0 END) AS q3,
       SUM(CASE WHEN quarter = 'Q4' THEN sales_amount ELSE 0 END) AS q4
FROM sales_detail
GROUP BY product;

CASE WHEN写法在旧版本Oracle中通用，执行计划与PIVOT接近，但列名和聚合逻辑散落在多个表达式里，不够紧凑。PIVOT把聚合和列映射集中在一起，代码更容易维护。列转行传统上用UNION ALL，需要扫描多次表，而UNPIVOT只扫描一次，数据量大时UNPIVOT效率更高。不过UNION ALL可以保留NULL行，而UNPIVOT默认去掉NULL，若业务需要保留空值要加INCLUDE NULLS。

从优化器角度看，两者最终都可能转换为表扫描加分组或UNION，性能差异不一定巨大，关键仍在于索引、过滤条件、统计信息。比如PIVOT子查询如果只针对当年数据，应该先加WHERE条件，否则Oracle会扫描全表再过滤。动态列需求建议避免在数据库层频繁拼接SQL，可把行列转换交给应用层或前端，降低SQL注入和解析开销风险。

五、容易踩的几个坑

第一是NULL处理。PIVOT聚合时若没有数据，结果为NULL，如需显示0要使用NVL；UNPIVOT默认丢弃NULL行，有时会让分析结果少行，需要明确INCLUDE NULLS。第二是列名冲突。PIVOT生成的列别名如果与已有列重名，Oracle会报错，需要调整别名；UNPIVOT生成的值列名也不能和原表列冲突。

第三是类型匹配。PIVOT的IN列表中的值必须与FOR列类型兼容，例如日期类型不能和字符串直接混用；UNPIVOT要求被拆分的列类型一致，否则要先转换。第四是版本限制。PIVOT和UNPIVOT从Oracle 11g开始支持，若维护旧库需改用CASE WHEN和UNION ALL。还要注意某些云数据库或异构数据库的语法差异，迁移时需要重写。

最后，不要忽视分组键。因为PIVOT隐式分组，若子查询包含无关列，结果会多出大量行，排查时先核对子查询的SELECT列表是否只包含分组列、FOR列和聚合列。掌握这些细节后，PIVOT和UNPIVOT就能成为日常Oracle开发中高效的转置工具。

Oracle PIVOT行转列UNPIVOT修改时间：2026-10-02 02:41:39