PIVOT (DML 九)

PIVOT

簡單來說就是行列轉換

※先看資料

SELECT deptno, sum(sal)
FROM emp GROUP BY deptno ORDER BY deptno;
    
SELECT deptno, job, sum(sal)
FROM emp GROUP BY deptno, job ORDER BY deptno;

※這兩個 SQL 分別 GROUP BY 一個和二個欄位

※結果：

※原來的做法使用decode

SELECT 
    deptno,
    sum(sal),
    sum(decode(job, 'CLERK', sal, 0)) C,
    sum(decode(job, 'SALESMAN', sal, 0)) S,
    sum(decode(job, 'PRESIDENT', sal, 0)) P,
    sum(decode(job, 'MANAGER', sal, 0)) M,
    sum(decode(job, 'ANALYST', sal, 0)) A
FROM emp GROUP BY deptno ORDER BY deptno;
    
SELECT 
    deptno,
    job,
    sum(sal),
    sum(decode(job, 'CLERK', sal, 0)) C,
    sum(decode(job, 'SALESMAN', sal, 0)) S,
    sum(decode(job, 'PRESIDENT', sal, 0)) P,
    sum(decode(job, 'MANAGER', sal, 0)) M,
    sum(decode(job, 'ANALYST', sal, 0)) A
FROM emp GROUP BY deptno, job ORDER BY deptno;

※結果：

※可以看出SUM的結果是上往下，而經過decode變成左往右了(SUM的值等於右邊五個欄位的加總)，也就是行列轉換

※語法

SELECT * FROM (
    SELECT job, sal FROM emp
)
PIVOT(
    sum(sal) FOR job IN(
        'CLERK' C,
        'SALESMAN' S,
        'PRESIDENT' P,
        'MANAGER' M,
        'ANALYST' A
    )
);

※結果：

※一定要用子查詢，否則編譯不會過

※這結果沒有GROUP BY，也就是CLERK的加總為C，SALESMAN的加總為S，依此類推

※隱藏的 GROUP BY

SELECT * FROM (
    SELECT deptno, job, sal FROM emp
)
PIVOT(
    sum(sal) FOR job IN(
        'CLERK' C,
        'SALESMAN' S,
        'PRESIDENT' P,
        'MANAGER' M,
        'ANALYST' A
    )
)
ORDER BY deptno;
    
    
    
SELECT * FROM (
  SELECT deptno, job j, sal, job FROM emp e
)
PIVOT(
    sum(sal) s FOR job IN(
        'CLERK' C,
        'SALESMAN' S,
        'PRESIDENT' P,
        'MANAGER' M,
        'ANALYST' A
    )
)
ORDER BY deptno;

※第一個SQL的job和sal，因為PIVOT會用到，所以不會GROUP BY，而deptno沒有用到，會自動的GROUP BY

※如果要GROUP BY多個就多打幾個欄位，但如果要打的欄位被PIVOT用了，所以只好如第二個SQL，打個別名

※PIVOT裡的IN一定要事先知道，無法用子查詢、ANY；如果用「>」、「NOT IN」編譯都不會過

※結果：

※這結果和使用decode一樣，只差在null的部分，但我用NVL無效

※多個彙總函數

SELECT * FROM (
    SELECT deptno, job, sal FROM emp
)
PIVOT(
    sum(sal) s, max(sal) m FOR job IN(
        'CLERK' C,
        'SALESMAN' S,
        'PRESIDENT' P,
        'MANAGER' M,
        'ANALYST' A
    )
)
ORDER BY deptno;

※結果：

※可以觀察它的命名方式

※多個條件

SELECT * FROM (
    SELECT e.deptno dno, job, sal, loc FROM emp e, dept d
    WHERE e.deptno = d.deptno
)
PIVOT(
    sum(sal) s FOR (job,loc) IN(
        ('CLERK', 'CHICAGO') C,
        ('SALESMAN', 'CHICAGO') S,
        ('PRESIDENT', 'CHICAGO') P,
        ('MANAGER', 'CHICAGO') M,
        ('ANALYST', 'CHICAGO') A
    )
)
ORDER BY dno;

※結果：

※注意 loc 有四個值，我只打出一個值(CHICAGO)

※轉成XML

SELECT * FROM (
    SELECT deptno, job, sal FROM emp
)
PIVOT XML(
    sum(sal) s FOR job IN(ANY)
)
ORDER BY deptno;

※結果：

※IN裡面放ANY，SOME和ALL都不行，而且只能配合PIVOT XML

※因為Oracle SQL Developer太長了，也不好觀看，所以我排列如下：

<PivotSet>
    <item>
        <column name = "JOB">CLERK</column>
        <column name = "S">1300</column>
    </item>
    
    <item>
        <column name = "JOB">MANAGER</column>
        <column name = "S">2450</column>
    </item>
    
    <item>
        <column name = "JOB">PRESIDENT</column>
        <column name = "S">5000</column>
    </item>
</PivotSet>

※UNPIVOT

也就是PIVOT的相反

WITH t AS (
    SELECT * FROM (
        SELECT deptno, job, sal FROM emp
    )
    PIVOT(
        sum(sal) S_SA FOR job IN(
            'CLERK' C,
            'SALESMAN' S,
            'PRESIDENT' P,
            'MANAGER' M,
            'ANALYST' A
        )
    )
    ORDER BY deptno
)
    
SELECT * FROM t
UNPIVOT(
    S_SA FOR job IN(
        C_S_SA as 'CLERK',
        S_S_SA as 'SALESMAN',
        P_S_SA as 'PRESIDENT',
        M_S_SA as 'MANAGER',
        A_S_SA as 'ANALYST'
    )
)
ORDER BY deptno;

※首先使用WITH將上面的「隱藏的GROUP BY」的寫法複製進去，然後下面再用UNPIVOT

※UNPIVOT裡面的名字，可以先執行上面的PIVOT，看到欄位名稱後打進去即可，但在UNPIVOT裡面，「as」一定要打，不然編譯不會過

※結果：

※可以看出結果又轉回來了

※INCLUDE NULLS／EXCLUDE NULLS

WITH t AS (
    SELECT * FROM (
        SELECT deptno, job, sal FROM emp
    )
    PIVOT(
        sum(sal) S_SA FOR job IN(
            'CLERK' C,
            'SALESMAN' S,
            'PRESIDENT' P,
            'MANAGER' M,
            'ANALYST' A
        )
    )
    ORDER BY deptno
)
SELECT * FROM t
UNPIVOT INCLUDE NULLS(
    S_SA FOR job IN(
        C_S_SA as 'CLERK',
        S_S_SA as 'SALESMAN',
        P_S_SA as 'PRESIDENT',
        M_S_SA as 'MANAGER',
        A_S_SA as 'ANALYST'
    )
)
ORDER BY deptno;

※這語法和上面一樣，只是在UNPIVOT後面多個 INCLUDE NULLS

※結果：

※可以看出null也顯示出來了，而 EXCLUDE NULLS是預設的，
也就是打PIVOT等同 PIVOT EXCLUDE NULLS