Oracle11gSQL新功能pivot/unpivot

　　 g以前的行列轉換

　　領袖又說了溫故而知新那就讓我們先看看g以前是怎麼實現地行列轉換一直當作甄別老手和新手的試金石面試的時候面試官不問這個都不好意思張嘴Itpub的Oracle開發版更是每隔十天半個月就有人問這個你說重要不重要

　　假設有表emp_phone如下

NAME
TYPE
PHONE
張三


張三


張三


李四


李四


馬五u


馬五


王二（沒麻子）



 
 

　　表裡放著張三李四王二麻子等等主人翁的電話號碼（TYPE //分別對應家／辦公室／手機）如果要把每個人的所有電話放在一行上就是行轉列了結果如下

NAME
HOME
OFFICE
MOBILE
張三



李四

 

馬五


 
王二（沒麻子）
 

　　寫這個SQL的技巧就是按姓名分組然後使每一組每一類的電話號碼最多只有一個裡邊用到的分組函數都是聾子的耳朵－擺設用MAX可以MIN也行

　　這個查詢寫出來就是          SELECT
        name
        MAX(decode(type phone)) Home
        MAX(decode(type phone)) Office
        MAX(decode(type 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 <= )
        WHERE
        DECODE (lvl home office mobile) IS NOT NULL ／

　　 g 自帶的行列轉換

　　旁邊那個帶眼鏡說的就是你眼珠子直勾勾的怎麼了？上面的沒看懂? 要是以前我老先生就得語重心長地教育你那麼重要的東西沒看懂將來想不想換工作了？但現在這話就說不出口了因為g的SQL自己就帶這個了

　　g在SELECT語句中新加了關鍵詞PIVOT和UNPIVOT用這兩個關鍵詞重寫上面的兩個查詢就變成這個樣子的了

　　行變列          SELECT * FROM emp_phone
        PIVOT (
        MAX(phone) for type IN ( as home as office as mobile)
        )
        ／

　　PIVOT以後的字句都是新加的但萬變不離其宗還是要用到分組函數IN後邊是按type的不同值映射成不同的列簡單吧？

　　列變行這是UNPIVOT的工作寫法如下          SELECT * FROM emp_phone_x
        UNPIVOT (
        phone FOR type in (HOME AS OFFICE AS MOBILE AS )
        )
        /

　　這裡是把不同的列轉換成不同的type的數值

　　再用SCOTT用戶裡的EMP表做個例子列出各部門之間工資總和          SELECT * FROM
        (
        (SELECT sal deptno FROM emp)
        PIVOT (
        SUM(sal) FOR deptno IN ( as dept_ as dept_ as dept_)
        )
        )
        /
        DEPT_ DEPT_ DEPT_
       
       

　　再往深裡想前邊的所有例子都有一個局限電話的type和emp的deptno都是有限的可窮舉的如果這些列都是可隨時可添加的又該怎麼辦呢？g以前肯定是要動用動態SQL的法寶那g又是怎麼處理的呢？剛看SQL參考手冊的時候看到裡邊豁然寫著IN後邊可以接子查詢或ANY當時是佩服的眼淚嘩嘩的迫不及待趕緊試一試          SELECT * FROM
        (
        (SELECT sal deptno FROM emp)
        PIVOT (
        SUM(sal) FOR deptno IN (SELECT deptno FROM dept)
        )
        )
        /
        ERROR at line :
        ORA: missing expression
        SELECT * FROM
        (
        (SELECT sal deptno FROM emp)
        PIVOT (
        SUM(sal) FOR deptno IN (ANY)
        )
        )
        /
        ERROR at line :
        ORA: 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></column>
        <column name = SUM(SAL)></column>
        </item>
        <item>
        <column name = DEPTNO></column>
        <column name = SUM(SAL)></column>
        </item>
        <item>
        <column name = DEPTNO></column>
        <column name = SUM(SAL)></column>
        </item>
        </PivotSet>

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

From:http://tw.wingwit.com/Article/program/Oracle/201311/16652.html