摘要：(简要介绍Oracle11g sql的新功能 pivot/unpivot 的使用方法以及如何使用它们做到行列转换.
蓄势以久的Oracle 11g 终于七月敲锣打鼓隆重推出，接下来就是网上漫天盖地的新功能介绍。11g面向开发的新功能本来就不多，掰着手指头也就是pivot和查询结果缓存的新Hint。本以为不久就会有人详述，谁知盼到两眼欲穿，大家还是翻来覆去的讨论DBA的自动分区之类。Oracle自己的门脸上到是每每用客气的冷漠写着“马上就来” (coming soon)，可这马上都转眼都快马上了一个月了，还迟迟不见盖头掀起来。
(http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/index.html?RSSid=RSS_otn_articles)
伟人说过“自己动手，丰衣足食”，等不来，咱就自己来。没吃过猪肉，还没见过猪跑？说干就干，下载安装再加一本“sql参考手册”，齐了。这新花活到底怎么使，且听我从头道来。。。
1. 11g以前的行列转换
领袖又说了：“温故而知新”。那就让我们先看看11g以前是怎么实现地。行列转换一直当作甄别老手和新手的试金石，面试的时候面试官不问这个都不好意思张嘴。Itpub的Oracle开发版更是每隔十天半个月就有人问这个，你说重要不重要。
假设有表emp_phone如下：
NAME TYPE PHONE
张三 1 1234-5678
张三 2 4567-7890
张三 3 6000-1001
李四 1 2123-1237
李四 3 6001-5600
马五u 1 3248-1378
马五 2 3423-3948
王二(没麻子) 2 2890-1245
。。。
表里放着张三李四王二麻子等等主人翁的电话号码。(TYPE 1/2/3分别对应家／办公室／手机)。如果要把每个人的所有电话放在一行上，就是行转列了。结果如下：
NAME HOME OFFICE MOBILE
张三 1234-5678 4567-7890 6000-1001
李四 2123-1237 6001-5600
马五 3248-1378 3423-3948
王二(没麻子) 2890-1245
写这个sql的技巧就是按姓名分组，然后使每一组每一类的电话号码最多只有一个，里边用到的分组函数都是聋子的耳朵－摆设。用MAX可以，MIN也行。
这个查询写出来就是：
SELECT
name,
MAX(decode(type,1,phone)) Home,2,phone)) Office,3,phone)) Mobile
FROM
emp_phone
GROUP BY
Name
／
那位看官说了：“能不能再变回去？”能，不能戏法不就漏了不是？
这儿要用到另一的技巧就是笛卡尔乘积，将一行复制成三行，每一行取一个类型的电话
偷个懒儿把上边的结果表叫emp_phone_x，把列还原成行的sql：
SELECT
NAME,
DECODE (lvl,home,office,mobile) phone
FROM
emp_phone_x,
(SELECT LEVEL lvl
FROM DUAL
CONNECT BY LEVEL <= 3)
WHERE
DECODE (lvl,mobile) IS NOT NULL ／
转来转去，一来一往，阴阳辟易，详推用意终何在，延年益寿不老春。往玄里说，就是老祖宗老挂在嘴边上的“道”。那位又说了：“这都哪儿跟哪儿啊？怎么扯到太极拳上去了”。
2. 11g 自带的行列转换
旁边那个带眼镜，说的就是你，眼珠子直勾勾的怎么了？上面的没看懂? 要是以前，我老先生就得语重心长地教育你，那么重要的东西没看懂，将来想不想换工作了？但现在这话就说不出口了，因为11g的sql自己就带这个了。
11g在SELECT语句中新加了关键词PIVOT和UNPIVOT，用这两个关键词，重写上面的两个查询，就变成这个样子的了：
行变列：
SELECT * FROM emp_phone
PIVOT (
MAX(phone) for type IN (1 as home,2 as office,2 as mobile)
)
／
PIVOT以后的字句都是新加的。但万变不离其宗，还是要用到分组函数。IN后边是按type的不同值映射成不同的列。简单吧？
列变行，这是UNPIVOT的工作，写法如下： SELECT * FROM emp_phone_x UNPIVOT ( phone FOR type in (HOME AS 1,OFFICE AS 2,MOBILE AS 3) ) / 这里是把不同的列转换成不同的type的数值。再用SCOTT用户里的EMP表做个例子，列出各部门之间工资总和： SELECT * FROM ( (SELECT sal,deptno FROM emp) PIVOT ( SUM(sal) FOR deptno IN (10 as dept_10,20 as dept_20,30 as dept_30) ) ) / DEPT_10 DEPT_20 DEPT_30 ---------- ---------- ---------- 8750 10875 9400
再往深里想，前边的所有例子都有一个局限，电话的type和emp的deptno都是有限的、可穷举的。如果这些列都是可随时可添加的，又该怎么办呢？11g以前肯定是要动用动态sql的法宝。那11g又是怎么处理的呢？刚看sql参考手册的时候，看到里边豁然写着IN后边可以接子查询或ANY，当时是佩服的眼泪哗哗的，迫不及待赶紧试一试：
SELECT * FROM
(
(SELECT sal,deptno FROM emp)
PIVOT (
SUM(sal) FOR deptno IN (SELECT deptno FROM dept)
)
)
/
ERROR at line 5:
ORA-00936: missing expression
SELECT * FROM
(
(SELECT sal,deptno FROM emp)
PIVOT (
SUM(sal) FOR deptno IN (ANY)
)
)
/
ERROR at line 5:
ORA-00936: missing expression
这一下又变成拔凉拔凉的，这么大个ORACLE也不能无耻到这个地步吧？正准备再确认一下手册，抓他个人赃俱获，突然有发现里边豁然写着： A subquery is used only in conjunction with the XML keyword… The ANY keyword is used only in conjunction with the XML keyword… 学习不认真，该打。原来是给生成XML串用的，正确用法如下： SELECT * FROM ( (SELECT sal,deptno FROM emp) PIVOT XML ( SUM(sal) FOR deptno IN (ANY) ) ) 1087502010875309400
这个东西的结果具体怎么用就留给大家做作业了。反正XML咱也不熟，借这个机会就下了。。。
2012-11-2922:51:21 我自己的测试
注意：建表插入的时候varchar字符型必须要加上'',
insert into s1 values('yuan',english80);
注意这里max的是分数，in的是subject
返回来
SELECT * FROM
( FROMs1
PIVOT (MAX(score) for subject IN( chinese' chinese,0); line-height:1.5!important">math' math,0); line-height:1.5!important">'english ) ) )
UNPIVOT ( scoreFOR subject IN ( chinese,math,english ) )
注意这里 最后一句的in chinese 之类，chinese不需要加''
成绩表(CHENGJI)如下：
XINGMING
KEMU
CHENGJI
1
张三
语文
89.00
2
张三
数学
98.00
3
张三
英语
88.00
4
李四
语文
90.00
5
李四
数学
89.00
6
李四
英语
60.00
7
王五
数学
66.00
8
王五
英语
99.00
方法一、DECODE
SELECTCJ.XINGMING,
SUM(DECODE(CJ.KEMU,'语文',CJ.CHENGJI,0))语文,'数学',0))数学,'英语',0))英语
FROMCHENGJICJ
GROUPBYCJ.XINGMING
XINGMING
语文
数学
英语
1
张三
89
98
88
2
王五
0
66
99
3
李四
90
89
60
方法二、CASE WHEN
SUM(CASEWHENCJ.KEMU='语文'THENCJ.CHENGJIELSE0END)语文,85); font-weight:bold">WHENCJ.KEMU='数学'END)数学,85); font-weight:bold">WHENCJ.KEMU='英语'END)英语
XINGMING
语文
数学
英语
1
张三
89
98
88
2
王五
0
66
99
3
李四
90
89
60
总结
如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
