pivot、unpivot實現oracle行列轉換

pwz1688發表於2015-07-16
Oracle
11g之後，oracle增加了pivot和unpivot語句，可以很方便的完成行列轉換。
先來看看pivot的語法是

點選(此處)摺疊或開啟

  1. SELECT ....
  2. FROM <table-expr>
  3.    PIVOT
  4.      (
  5.       aggregate-function(<column>)
  6.       FOR <pivot-column> IN (<value1>, <value2>,..., <valuen>)
  7.         ) AS <alias>
  8. WHERE .....
來看個例子，emp表中資料如下：

點選(此處)摺疊或開啟

  1. SQL> select empno,ename,job,sal,deptno from emp order by deptno,job;

  3.      EMPNO ENAME JOB SAL DEPTNO
  4. ---------- ---------- --------- ---------- ----------
  5.       7934 MILLER CLERK 1300 10
  6.       7782 CLARK MANAGER 2450 10
  7.       7839 KING PRESIDENT 5000 10
  8.       7902 FORD ANALYST 3000 20
  9.       7369 SMITH CLERK 800 20
  10.       7566 JONES MANAGER 2975 20
  11.       7900 JAMES CLERK 950 30
  12.       7698 BLAKE MANAGER 2850 30
  13.       7844 TURNER SALESMAN 1500 30
  14.       7521 WARD SALESMAN 1250 30
  15.       7499 ALLEN SALESMAN 1600 30

  17.      EMPNO ENAME JOB SAL DEPTNO
  18. ---------- ---------- --------- ---------- ----------
  19.       7654 MARTIN SALESMAN 1250 30

  21. 已選擇12行。
現在查詢各部門各工種的總薪水，如下：

點選(此處)摺疊或開啟

  1. SQL> select deptno,job,sum(sal) from emp group by deptno,job order by 1,2;

  3.     DEPTNO JOB SUM(SAL)
  4. ---------- --------- ----------
  5.         10 CLERK 1300
  6.         10 MANAGER 2450
  7.         10 PRESIDENT 5000
  8.         20 ANALYST 3000
  9.         20 CLERK 800
  10.         20 MANAGER 2975
  11.         30 CLERK 950
  12.         30 MANAGER 2850
  13.         30 SALESMAN 5600

  15. 已選擇9行。
現在需要將每個工種作為一列顯示就更一目瞭然，這就需要進行行列轉換了，先看11G之前，利用decode函式來實現行列轉換
 

點選(此處)摺疊或開啟

  1. SQL> edit
  2. 已寫入 file afiedt.buf

  4.   1 select deptno,
  5.   2 sum(decode(job,'CLERK',sal,0)) as CLERK_sal,
  6.   3 sum(decode(job,'MANAGER',sal,0)) as MANAGER_sal,
  7.   4 sum(decode(job,'PRESIDENT',sal,0)) as PRESIDENT_sal,
  8.   5 sum(decode(job,'ANALYST',sal,0)) as ANALYST_sal,
  9.   6 sum(decode(job,'SALESMAN',sal,0)) as SALESMAN_sal
  10.   7* from emp group by deptno order by 1
  11. SQL> /

  13.     DEPTNO CLERK_SAL MANAGER_SAL PRESIDENT_SAL ANALYST_SAL SALESMAN_SAL
  14. ---------- ---------- ----------- ------------- ----------- ------------
  15.         10 1300 2450 5000 0 0
  16.         20 800 2975 0 3000 0
  17.         30 950 2850 0 0 5600

  19. SQL>

再看看11G新特性pivot實現行列轉換，如下：

點選(此處)摺疊或開啟

  1. SQL> edit;
  2. 已寫入 file afiedt.buf

  4.   1 select * from(
  5.   2 select deptno,job,sal from emp
  6.   3 )
  7.   4 pivot(
  8.   5 sum(sal) for job in(
  9.   6 'PRESIDENT' as PRESIDENT_SAL,
  10.   7 'MANAGER' as MANAGER_SAL,
  11.   8 'ANALYST' as ANALYST_SAL,
  12.   9 'CLERK' as CLERK_SAL,
  13.  10 'SALESMAN' as SALESMAN_SAL
  14.  11 )
  15.  12* ) order by 1
  16. SQL> /

  18.     DEPTNO PRESIDENT_SAL MANAGER_SAL ANALYST_SAL CLERK_SAL SALESMAN_SAL
  19. ---------- ------------- ----------- ----------- ---------- ------------
  20.         10 5000 2450 1300
  21.         20 2975 3000 800
  22.         30 2850 950 5600

  24. SQL>

實際上，oracle對pivot子句中出現的列以外的列做了一個隱式的group by.現在，如果想要再結果中增加1列，顯示部門的薪水總合，可以這麼做

點選(此處)摺疊或開啟

  1. SQL> edit
  2. 已寫入 file afiedt.buf

  4.   1 select * from(
  5.   2 select deptno,job,sal,sum(sal) over(partition by deptno) sal_total from emp
  6.   3 )
  7.   4 pivot(
  8.   5 sum(sal) as total_sal for job in(
  9.   6 'PRESIDENT' as P,
  10.   7 'MANAGER' as M,
  11.   8 'ANALYST' as A,
  12.   9 'CLERK' as C,
  13.  10 'SALESMAN' as S
  14.  11 )
  15.  12* ) order by 1
  16. SQL> /

  18.     DEPTNO SAL_TOTAL P_TOTAL_SAL M_TOTAL_SAL A_TOTAL_SAL C_TOTAL_SAL S_TOTAL_SAL
  19. ---------- ---------- ----------- ----------- ----------- ----------- -----------
  20.         10 8750 5000 2450 1300
  21.         20 6775 2975 3000 800
  22.         30 9400 2850 950 5600

  24. SQL>
說明：
1）oracle對pivot子句中出現的列以外的列，也就是deptno和SAL_TOTAL做了隱式的group by.這裡用了分析函式，對於每個deptno，SAL_TOTAL是唯一的，所以group by的結果還是3行。
2）oracle會拼接列名 = for字句中別名+聚合函式別名，比如'P'+'_'+'SAL_TOTAL'
可以指定多個聚合函式，如統計薪水總合與人數總合，如下：

點選(此處)摺疊或開啟

  1. SQL> edit;
  2. 已寫入 file afiedt.buf

  4.   1 select * from(
  5.   2 select deptno,job,sal from emp
  6.   3 )
  7.   4 pivot(
  8.   5 sum(sal) as sal_total,count(sal) as emp_total for job in(
  9.   6 'PRESIDENT' as PRESIDENT,
  10.   7 'MANAGER' as MANAGER
  11.   8 )
  12.   9* ) order by 1
  13. SQL> /

  15.     DEPTNO PRESIDENT_SAL_TOTAL PRESIDENT_EMP_TOTAL MANAGER_SAL_TOTAL MANAGER_EMP_TOTAL
  16. ---------- ------------------- ------------------- ----------------- -----------------
  17.         10 5000 1 2450 1
  18.         20 0 2975 1
  19.         30 0 2850 1

for子句可以指定多列，如想統計salesman和clerk員工，其mgr為7902和7698各自的人數，如下：

點選(此處)摺疊或開啟

  1. SQL> edit;
  2. 已寫入 file afiedt.buf

  4.   1 select * from(
  5.   2 select deptno,job,mgr from emp
  6.   3 )
  7.   4 pivot(
  8.   5 count(mgr) as emp_total for(job,mgr) in(
  9.   6 ('SALESMAN', '7902') as SALESMAN_7902,
  10.   7 ('SALESMAN', '7698') as SALESMAN_7698,
  11.   8 ('CLERK', '7902') as CLERK_7902,
  12.   9 ('CLERK', '7698') as CLERK_7698
  13.  10 )
  14.  11* )
  15. SQL> /

  17.     DEPTNO SALESMAN_7902_EMP_TOTAL SALESMAN_7698_EMP_TOTAL CLERK_7902_EMP_TOTAL CLERK_7698_EMP_TOTAL

  19. ---------- ----------------------- ----------------------- -------------------- --------------------

  21.         30 0 4 0 1

  23.         20 0 0 1 0

  25.         10 0 0 0 0

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21251711/viewspace-1734828/，如需轉載，請註明出處，否則將追究法律責任。

相關文章