OracleのPIVOT句

SQLで横持ちにデータを集計したい時に、PIVOT句を使うと便利だったのでメモ。

【元ネタ】
Oracle 11g検証 隠れた新機能検証　その１｜おら! オラ! Oracle｜技術情報｜株式会社インサイトテクノロジー

Oracle 11g R1新機能のPivotとUnPivot（1/5）：CodeZine

OracleのSCOTTスキーマにある従業員テーブルで、JOB(仕事)とDNAME(部門)ごとに集計したい場合、下記のSQLで書ける。

select emp.deptno,dname
,sum(decode(job,'CLERK' ,sal,null)) "CLERK"
,sum(decode(job,'MANAGER' ,sal,null)) "MANAGER"
,sum(decode(job,'PRESIDENT',sal,null)) "PRESIDENT"
,sum(decode(job,'ANALYST' ,sal,null)) "ANALYST"
,sum(decode(job,'SALESMAN' ,sal,null)) "SALESMAN"
from emp,dept
where emp.deptno = dept.deptno
group by emp.deptno,dname
order by emp.deptno,dname;

PIVOT句を使うともっと簡単に書ける。

select * from ( select emp.deptno,dname,job,sal
from emp,dept
where emp.deptno = dept.deptno )
pivot ( sum(sal) for job
in ('CLERK','MANAGER','PRESIDENT','ANALYST','SALESMAN') )
order by deptno,dname;

PIVOT句を使う状況は、月別に横持ちでデータを表示したい時がある。
例えば、月別に部門ごとに売上金額を集計したい時など。
いわゆるクロス集計で使えると思う。

クロス集計されたデータをCSV出力すれば、Excelで簡単にグラフ化して、レポート用に整形できる。
チケット駆動開発ならば、ステータスとトラッカー・カテゴリ・バージョン・担当者のクロス集計の出力にも使えそうな機能だろう。

再帰SQLと同様にSQLは実は奥が深いのだ。

再帰SQL: プログラマの思索

再帰SQL part2: プログラマの思索