轉自 http://tech.it168.com/db/2007-09-07/200709071114985.shtml

【IT168技術文件】

摘要：（簡要介紹Oracle11g SQL的新功能 pivot/unpivot 的使用方法以及如何使用它們做到行列轉換.

蓄勢以久的Oracle 11g 終於七月敲鑼打鼓隆重推出，接下來就是網上漫天蓋地的新功能介紹。11g面向開發的新功能本來就不多，掰著手指頭也就是pivot和查詢結果快取的新Hint。本以為不久就會有人詳述，誰知盼到兩眼欲穿，大家還是翻來覆去的討論DBA的自動分割槽之類。Oracle自己的門臉上到是每每用客氣的冷漠寫著“馬上就來” (coming soon)，可這馬上都轉眼都快馬上了一個月了，還遲遲不見蓋頭掀起來。

（）
偉人說過“自己動手，豐衣足食”，等不來，我們就自己來。沒吃過豬肉，還沒見過豬跑？說幹就幹，下載安裝再加一本“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,
MAX(decode(type, 2, phone)) Office,
MAX(decode(type, 3, phone)) Mobile
FROM
emp_phone
GROUP BY
Name
／

那位看官說了：“能不能再變回去？”能，不能戲法不就漏了不是？
這兒要用到另一的技巧就是笛卡爾乘積，將一行復製成三行，每一行取一個型別的電話
偷個懶兒把上邊的結果表叫emp_phone_x，把列還原成行的SQL：

SELECT
NAME,
DECODE (lvl, 1, home, 2, office, 3, mobile) phone
FROM
emp_phone_x,
(SELECT LEVEL lvl
FROM DUAL
CONNECT BY LEVEL <= 3)
WHERE
DECODE (lvl, 1, home, 2, office, 3, 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)
)
)
<PivotSet><item><column name = "DEPTNO">10column>
<column name = "SUM(SAL)">8750column>item><item>
<column name = "DEPTNO">20column><column name = "SUM(SAL)">10875column>item><item>
<column name = "DEPTNO">30column><column name = "SUM(SAL)">9400column>item>PivotSet>

這個東西的結果具體怎麼用就留給大家做作業了。反正XML我們也不熟，借這個機會就下了。。。

[@more@]

oracle 11g 使用 pivot/unpivot 行列轉換

相關文章