oracle 11g中的pivot和unpivot转换操作.pdf

对于pivot 和unpivot 这个Oracle 11g 的新功能一直就想总结一下，正好赶上论坛中有很多人 会提问相关的问题，于是利用空闲时间翻译了一篇相关的网络文章。链接如下： /display.php?id=506 ITPuber ：LuiseDalian 翻译 时间：20 12.11.29 1 / 41 1 Oracle 11g 中的pivot 和unpivot 查询 转换查询包括将行转换成列(pivot)和将列转换成行(unpivot)，从而产生交叉表格式的结果。 转换是一项通用的技术，尤其对于报表来讲，正是由于该项技术使得多年来针对 Oracle 的不同 版本，可以使用SQL 语句产生转换查询的结果集。从Oracle 11g 开始，包含了显式的转换查询 支持，即PIVOT 和UNPIVOT 关键字。这是对SELECT 语句的扩展。 1.1 pivot 这里我们从pivot 操作开始。大多数的开发者首先要熟悉转换数据：即多行被聚合，然后转换成 列，每一列表示聚合数据的不同范围。语法： SELECT ... FROM ... PIVOT [XML] ( pivot_clause pivot_for_clause pivot_in_clause ) WHERE ... 除了新的pivot 关键字外，可以看到3 个新的pivot 子句，描述如下：  pivot_clause ：定义被聚合的列（pivot 是一种聚合操作）  pivot_for_clause ：定义被分组和转换的列。  pivot_in_clause ：定义pivot_for_clause 子句中列的过滤器（例如使用值的范围来限制结果）。 pivot_in_clause 中每个值的聚合都将被转换成单独的列 1.1.1 一个简单的例子 这个例子是对pivot 语法的简单的演示，使用scott.emp 表，这里将通过department 和job 来汇总 salaries，然后转换对每个部门的汇总到它们各自的列上，在转换之前，先看一下基础数据 SELECT job, deptno, SUM (sal) AS sum_sal FROM emp GROUP BY job, deptno 2 / 41 ORDER BY job, deptno; 下面使用11g 的语法来转换数据，对于每个job 我们将sal 的汇总，对每个部门使用单独一列。 WITH pivot_data AS (SELECT deptno, job, sal FROM emp) SELECT * FROM PIVOT_data PIVOT ( SUM (sal) --pivot_clause FOR deptno --pivot_for_clause IN (10, 20, 30, 40) --pivot_in_cluase ); 可以看到对每个job 按部门对 sal 的汇总已经变成了列，关于这个例子有几点需要注意，语法和 结果：  本例中的pivot_clause 求sal 列的和，如果需要可以指定多列和可选的别名。  pivot 操作执行隐式的GROUP BY ，使用的列是没有出现在pivot_clause 中的列（本例为job 和 deptno ）。大多数的转换查询都会在列的子集上进行操作，所以会使用存储视图、内联视 图或子查询。  这里的pivot_for_clause ，表明我们只想转换deptno 的聚合。  这里的prvot_in_cluase ，指明deptno 值的范围，这里我们硬编码4 个值的列表，这也是为什 3 / 41 么会产生4 个转换列原因。如果省略别名，Oracle