Oracle 11g R1新機能のPivotとUnPivot

select文で縦横変換

2. Pivotで行列変換 （行⇒列）

　まずは、『外部結合の使い方』の（行→列）と同じ結果をPivotで取得してみます。サンプルを見てみましょう。

Courses

name	course
赤井	SQL入門
赤井	UNIX基礎
鈴木	SQL入門
工藤	SQL入門
工藤	Java中級
吉田	UNIX基礎
渡辺	SQL入門

Pivotの代用（集約関数とdecode関数）

select name,
max(decode(course,'SQL入門' ,'○')) as "SQL入門",
max(decode(course,'UNIX基礎','○')) as "UNIX基礎",
max(decode(course,'Java中級','○')) as "Java中級"
  from Courses
group by name
order by name;

出力結果

name	SQL入門	UNIX基礎	Java中級
吉田	null	○	null
工藤	○	null	○
赤井	○	○	null
渡辺	○	null	null
鈴木	○	null	null

　Pivotを使ったselect文で同じ結果を取得してみます。

Pivotを使ったselect文

select name,NewColumnName1,NewColumnName2,NewColumnName3
  from Courses
 pivot (max('○') for course in('SQL入門'  as NewColumnName1,
                                'UNIX基礎' as NewColumnName2,
                                'Java中級' as NewColumnName3))
order by name;

出力結果

name	NewColumnName1	NewColumnName2	NewColumnName3
吉田	null	○	null
工藤	○	null	○
赤井	○	○	null
渡辺	○	null	null
鈴木	○	null	null

　Pivotの構文は、下記のように理解しておくといいでしょう。

Pivotの構文

Pivot(集約関数 for 集約条件列 in(集約条件値1 as 集約後列名1,
                                 集約条件値2 as 集約後列名2,
                                 集約条件値3 as 集約後列名3));

　Pivotでは、集約関数で使用している列でなく、かつ集約条件列で使用している列でもない列で暗黙のgroup byが実行されます。

　上記のselect文においては、集約関数で使用している列は、なしです。なぜならmax('○')と固定値である'○'を指定しているからです。そして、集約条件列で使用している列は、course列です。よって、集約関数で使用している列でなく、かつ集約条件列で使用している列でもないNAME列で暗黙のgroup byが実行されます。

　Pivotの脳内のイメージは下記となります。暗黙のgroup byによる赤線をイメージし、for 集約条件列（上記のselect文では、for courseの部分）で黄緑線をイメージしてます。

SQLのイメージ

　下記のように、max('○')ではなく、固定値の'○'を記述すると、文法エラー 「ORA-56902: ピボット操作では集計関数を使用する必要があります」が発生してしまいます。

文法エラー

select name,NewColumnName1,NewColumnName2,NewColumnName3
  from Courses
 pivot ('○' for course in('SQL入門'  as NewColumnName1,
                           'UNIX基礎' as NewColumnName2,
                           'Java中級' as NewColumnName3));