在 Oracle 資料庫 11g 推出之前，您需要針對每個值透過使用decode 函式或case語句進行列轉換操作，
並將每個不同的值編寫為一個單獨的列。但是，該方法不是很不直觀也不是很方便。
oracle11g可以使用一種很好的新特性PIVOT和UNPIVOT,透過一種新的運算子以交叉表格式顯示任何查詢，
oracle為了實現更強大的資料倉儲應用，在SQL和PL/SQL方面有很大的加強。

SQL Server2005引入了很多迎合開發者口味的新特性，雖然改動不大，卻大大了減少了開發者的工作量，這種替使用者考慮的開發思路，值得稱讚。

[@more@]

[zt] Ref: http://www.cnblogs.com/end/archive/2008/06/13/1219875.html

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)
)
)

10
8750

20
10875

30
9400

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

------------------------------------------------------------

在SQL Server2000中，要實現行列轉換，需要綜合利用聚合函式和動態SQL，實現起來需要一定的技巧，所以在CSDN的SQL討論區裡可以看到大量詢問行列轉換如何實現的問題。到了2005中，使用新引進的關鍵字PIVOT/UNPIVOT，可以輕鬆實現行列轉換的需求。

好像Oracle11g也準備引入PIVOT/UNPIVOT特性，對於Oracle開發來說，It's a good news。

本文透過兩個簡單的例子展示PIVOT/UNPIVOT的用法。詳細的語法請參考聯機幫助。

PIVOT

建立測試表，插入測試資料

create table test(id int,name varchar(20),quarter int,profile int)
insert into test values(1,'a',1,1000)
insert into test values(1,'a',2,2000)
insert into test values(1,'a',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2,'b',2,3500)
insert into test values(2,'b',3,4200)
insert into test values(2,'b',4,5500)

select * from test
id name quarter profile
----------- -------------------- ----------- -----------
1 a 1 1000
1 a 2 2000
1 a 3 4000
1 a 4 5000
2 b 1 3000
2 b 2 3500
2 b 3 4200
2 b 4 5500

(8 row(s) affected)

利用PIVOT將個季度的利潤轉成橫向顯示：

select id,name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4])
)
as pvt

id name 一季度二季度三季度四季度
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500

(2 row(s) affected)

UNPIVOT

建立測試表，插入測試資料

drop table test

create table test(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)

insert into test values(1,'a',1000,2000,4000,5000)
insert into test values(2,'b',3000,3500,4200,5500)

select * from test

id name Q1 Q2 Q3 Q4
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500

(2 row(s) affected)

利用UNPIVOT，將同一行中四個季度的列資料轉換成四行資料：

select id,name,quarter,profile
from
test
unpivot
(
profile
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt

id name quarter profile
----------- -------------------- ---------- -----------
1 a Q1 1000
1 a Q2 2000
1 a Q3 4000
1 a Q4 5000
2 b Q1 3000
2 b Q2 3500
2 b Q3 4200
2 b Q4 5500

(8 row(s) affected)

oracle11g pivot 行列轉換 SQL Server 2005

相關文章