Oracle 11g中的pivot和unpivot转换操作.pdf
5墨值下载
2 / 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
3 / 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 个值的列表，这也是为什
of 40
5墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论