Oracle 的樞紐表 pivot 運算子轉換成 PGSQL 的 crosstab() 語句

遇到需要替換 Oracle 的 pivot() 運算子，換成 PGSQL 的 tablefunc 模組的 crosstab() 的需要，這裡紀錄一下。



找完資料，竟然發現這篇文章「Oracle的行列轉換_人人IT網」，可以直接測試和比照之前紀錄過的 PGSQL 樞紐表筆記！！先前已經筆記了 Postgres 的操作，不熟的話可以看文末連結。

下面是存上面網站借過來的 Oracle 範例與輸出結果

--Oracle
select * from
  (select deptno, job, sal from emp)
pivot(
  sum(sal) for job in (
  'PRESIDENT' as PRESIDENT_SAL,
  'MANAGER'   as MANAGER_SAL,
  'ANALYST'   as ANALYST_SAL,
  'CLERK'     as CLERK_SAL,
  'SALESMAN'  as SALESMAN_SAL
  )
) order by 1;

    DEPTNO PRESIDENT_SAL MANAGER_SAL ANALYST_SAL  CLERK_SAL SALESMAN_SAL
---------- ------------- ----------- ----------- ---------- ------------
        10          5000        2450                   1300
        20                      2975        6000       1900
        30                      2850                    950         5600

由於「範例資料」長的如出一轍，只要微調先前筆記就好

--Postgres 改寫後.
SELECT * FROM crosstab(
  $$select deptno, job,
    sum(sal) 
    from emp group by 1,2
    order by 1,2$$ , 
  $$select distinct job from emp
    order by job$$)
AS ( "deptno"        int,
     "PRESIDENT_SAL" number, 
     "MANAGER_SAL"   number,
     "ANALYST_SAL"   number,
     "CLERK_SAL"     number,
     "SALESMAN_SAL"  number );

 deptno | PRESIDENT_SAL | MANAGER_SAL | ANALYST_SAL | CLERK_SAL | SALESMAN_SAL 
--------+---------------+-------------+-------------+-----------+--------------
     10 |               |     1300.00 |     2450.00 |   5000.00 |             
     20 |       6000.00 |     1900.00 |     2975.00 |           |             
     30 |               |      950.00 |     2850.00 |           |      5600.00
(3 rows)

比較後
PGSQL 得使用字串的方式，把查詢送給函數；Oracle 則是透過一個 SQL 運算達成
PGSQL 需要產生 x 軸欄位資料（crosstab(,) 的第二個 SQL 字串參數）
PGSQL 需要指定「回傳的 Setof Records」的欄位名稱 AS ( . . . )

參考資料
之前筆記：在 PostgreSQL 裡面產生樞紐分析表
Oracle的行列轉換_人人IT網