oracle 00936 pivot,oracle 11g 使用 pivot/unpivot 行列转换

最新推荐文章于 2024-05-16 15:50:52 发布

Young斯基

最新推荐文章于 2024-05-16 15:50:52 发布

阅读量132

点赞数

文章标签： oracle 00936 pivot

【IT168技术文档】摘要：(简要介绍Oracle11g SQL的新功能 pivot/unpivot 的使用方法以及如何使用它们做到行列转换.

蓄势以久的Oracle 11g 终于七月敲锣打鼓隆重推出，接下来就是网上漫天盖地的新功能介绍。11g面向开发的新功能本来就不多，掰着手指头也就是pivot和查询结果缓存的新Hint。本以为不久就会有人详述，谁知盼到两眼欲穿，大家还是翻来覆去的讨论DBA的自动分区之类。Oracle自己的门脸上到是每每用客气的冷漠写着“马上就来” (coming soon)，可这马上都转眼都快马上了一个月了，还迟迟不见盖头掀起来。

()

伟人说过“自己动手，丰衣足食”，等不来，咱就自己来。没吃过猪肉，还没见过猪跑？说干就干，下载安装再加一本“SQL参考手册”，齐了。这新花活到底怎么使，且听我从头道来。。。

1. 11g以前的行列转换

领袖又说了：“温故而知新”。那就让我们先看看11g以前是怎么实现地。行列转换一直当作甄别老手和新手的试金石，面试的时候面试官不问这个都不好意思张嘴。Itpub的Oracle开发版更是每隔十天半个月就有人问这个，你说重要不重要。

假设有表emp_phone如下：

NAMETYPEPHONE

张三11234-5678

张三24567-7890

张三36000-1001

李四12123-1237

李四36001-5600

马五u13248-1378

马五23423-3948

王二(没麻子)22890-1245

。。。

表里放着张三李四王二麻子等等主人翁的电话号码。(TYPE 1/2/3分别对应家／办公室／手机)。如果要把每个人的所有电话放在一行上，就是行转列了。结果如下：

NAMEHOMEOFFICEMOBILE

张三1234-56784567-78906000-1001

李四2123-12376001-5600

马五3248-13783423-3948

王二(没麻子)2890-1245 写这个SQL的技巧就是按姓名分组，然后使每一组每一类的电话号码最多只有一个，里边用到的分组函数都是聋子的耳朵－摆设。用MAX可以，MIN也行。

这个查询写出来就是：

SELECTname,MAX(decode(type,1, phone)) Home,MAX(decode(type,2, phone)) Office,MAX(decode(type,3, phone)) MobileFROMemp_phoneGROUPBYName

／

那位看官说了：“能不能再变回去？”能，不能戏法不就漏了不是？

这儿要用到另一的技巧就是笛卡尔乘积，将一行复制成三行，每一行取一个类型的电话

偷个懒儿把上边的结果表叫emp_phone_x，把列还原成行的SQL：

SELECTNAME,

DECODE (lvl,1, home,2, office,3, mobile) phoneFROMemp_phone_x,

(SELECTLEVELlvlFROMDUAL

CONNECTBYLEVEL<=3)WHEREDECODE (lvl,1, home,2, office,3, mobile)ISNOTNULL／

转来转去，一来一往，阴阳辟易，详推用意终何在，延年益寿不老春。往玄里说，就是老祖宗老挂在嘴边上的“道”。那位又说了：“这都哪儿跟哪儿啊？怎么扯到太极拳上去了”。

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*FROMemp_phone_x

UNPIVOT (

phoneFORtypein(HOMEAS1, OFFICEAS2, MOBILEAS3)

)/这里是把不同的列转换成不同的type的数值。

再用SCOTT用户里的EMP表做个例子，列出各部门之间工资总和：SELECT*FROM(

(SELECTsal, deptnoFROMemp)

PIVOT (SUM(sal)FORdeptnoIN(10asdept_10,20asdept_20,30asdept_30)

)

)/DEPT_10 DEPT_20 DEPT_30---------- ---------- ----------8750108759400

再往深里想，前边的所有例子都有一个局限，电话的type和emp的deptno都是有限的、可穷举的。如果这些列都是可随时可添加的，又该怎么办呢？11g以前肯定是要动用动态SQL的法宝。那11g又是怎么处理的呢？刚看SQL参考手册的时候，看到里边豁然写着IN后边可以接子查询或ANY，当时是佩服的眼泪哗哗的，迫不及待赶紧试一试：

SELECT*FROM(

(SELECTsal, deptnoFROMemp)

PIVOT (SUM(sal)FORdeptnoIN(SELECTdeptnoFROMdept)

)

)/ERROR at line5:

ORA-00936: missing expressionSELECT*FROM(

(SELECTsal, deptnoFROMemp)

PIVOT (SUM(sal)FORdeptnoIN(ANY)

)

)/ERROR at line5:

ORA-00936: missing expression

这一下又变成拔凉拔凉的，这么大个ORACLE也不能无耻到这个地步吧？正准备再确认一下手册，抓他个人赃俱获，突然有发现里边豁然写着：