Oracle 11g的一个新特性
这个例子很简单,仅想通过下面的例子说明PIVOT的用法
【第一步 建测试表,姑且把这个叫做“表”,只是为了演示方便】
代码如下:
SELECT 10 * 2 AS A, 5 + 2 AS B, 'Domingo' AS C, ROWNUM AS RN
FROM DUAL
CONNECT BY ROWNUM < 5;
执行结果如下,之后所有的操作都依赖于下面的这个“表”
【第二步 演示开始,分别对下面First,Second的SQL语句执行】
◆First
WITH TMP AS(
SELECT 10 * 2 AS A, 5 + 2 AS B, 'Domingo' AS C
FROM DUAL
CONNECT BY ROWNUM < 5)
SELECT * FROM TMP;
Result
◆Second
WITH TMP AS(
SELECT 10 * 2 AS A, 5 + 2 AS B, 'Domingo' AS C
FROM DUAL
CONNECT BY ROWNUM < 5)
SELECT * FROM TMP PIVOT(SUM(A) FOR B IN (7 AS ER,8 AS BR,9 AS TS));
Result
【体会】
在◆Second的代码中,加入了PIVOT关键字,翻译一下 PIVOT(SUM(A) FOR B IN (7 AS ER,8 AS BR,9 AS TS))
对所有B列值为"7"的所有的A行求和。需要注意的是,PIVOT中一定会用到聚集函数,FOR和IN也是使用PIVOT中的关键字。
更多的资料也可参考
http://www.oracle.com/technetwork/cn/articles/11g-pivot-101924-zhs.html