Oracle 11g R1新機能のPivotとUnPivot

select文で縦横変換

3. UnPivotで行列変換 （列⇒行）

　次は、『外部結合の使い方』の（列→行）と同じ結果をUnPivotで取得してみます。

Personnel

employee	child_1	child_2	child_3
赤井	一郎	二郎	三郎
工藤	春子	夏子	null
鈴木	夏子	null	null
吉田	null	null	null

UnPivotの代用（union all）

select employee,child
from (select employee,child_1 as child from Personnel union all
      select employee,child_2 from Personnel union all
      select employee,child_3 from Personnel)
order by employee,child;

出力結果

employee	child
吉田	null
吉田	null
吉田	null
工藤	夏子
工藤	春子
工藤	null
赤井	一郎
赤井	三郎
赤井	二郎
鈴木	夏子
鈴木	null
鈴木	null

　UnPivotを使ったselect文で同じ結果を取得してみましょう。

UnPivotの代用（include nullsの指定を忘れた）

select employee,child,Key
  from Personnel
unpivot(child for Key in(child_1,child_2,child_3));

出力結果

employee	child	Key
工藤	春子	CHILD_1
工藤	夏子	CHILD_2
赤井	一郎	CHILD_1
赤井	二郎	CHILD_2
赤井	三郎	CHILD_3
鈴木	夏子	CHILD_1

　上記の出力結果では、childがnullの行が出力されてないですね。実は、UnPivotは、デフォルトでexclude nullsなため、UnPivot対象がnullの行も出力するには、include nullsを指定する必要があるのです。よって下記が答えとなります。

UnPivotを使った方法

select employee,child,Key
  from Personnel
unpivot include nulls(child for Key in(child_1,child_2,child_3))
order by employee,Key;

出力結果

employee	child	Key
吉田	null	CHILD_1
吉田	null	CHILD_2
吉田	null	CHILD_3
工藤	春子	CHILD_1
工藤	夏子	CHILD_2
工藤	null	CHILD_3
赤井	一郎	CHILD_1
赤井	二郎	CHILD_2
赤井	三郎	CHILD_3
鈴木	夏子	CHILD_1
鈴木	null	CHILD_2
鈴木	null	CHILD_3

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

UnPivotの構文

UnPivot(列値を表示する列名
for 元列の識別値を表示する列名 in(元列1,元列2,元列3));

　UnPivotの脳内のイメージは下記となります。行ごとに区切る赤線を引いて、元列2と元列3を、元列1の下に移動させる黄緑線を引いてます。

SQLのイメージ

　下記のように、元列の識別値を指定することもできます。child_1列の元列の識別値をBase1,child_2列の元列の識別値をBase2,child_3列の元列の識別値をBase3としてみます。

元列の識別値を指定

select employee,child,Key
  from Personnel
unpivot include nulls(child for Key in(child_1 as 'Base1',
                                       child_2 as 'Base2',
                                       child_3 as 'Base3'))
order by employee,Key;

出力結果

employee	child	Key
吉田	null	Base1
吉田	null	Base2
吉田	null	Base3
工藤	春子	Base1
工藤	夏子	Base2
工藤	null	Base3
赤井	一郎	Base1
赤井	二郎	Base2
赤井	三郎	Base3
鈴木	夏子	Base1
鈴木	null	Base2
鈴木	null	Base3

　下記のselect文のように、元列の識別値として数値型を指定し、order by句でのソートキーの指定に使うこともできます。

数値型のソートキーを持たせたUnPivot

select employee,child
  from Personnel
unpivot include nulls(child for SortKeys in(child_1 as 1,
                                            child_2 as 2,
                                            child_3 as 3))
order by employee,SortKeys;

出力結果

employee	child
吉田	null
吉田	null
吉田	null
工藤	春子
工藤	夏子
工藤	null
赤井	一郎
赤井	二郎
赤井	三郎
鈴木	夏子
鈴木	null
鈴木	null

　下記のselect文のように、元列の識別値として数値型と列名の両方を指定することもできます。要件に応じて使い分けるといいでしょう。

元列の識別値として数値型と列名の両方を指定

select employee,child,SortKeys,moto
  from Personnel
unpivot include nulls(child for (SortKeys,moto)
                      in(child_1 as (1,'moto1'),
                         child_2 as (2,'moto2'),
                         child_3 as (3,'moto3')))
order by employee,SortKeys;

UnPivotの代用法

　UnPivotの代用法としては、下記のような連番表とクロスジョインさせる方法があります。UnPivotが使えないOracle 10gなどでは、この方法を使うのがいいと思います。

UnPivotの代用（連番表とクロスジョイン）

select a.employee,
case b.Cnter
when 1 then a.child_1
when 2 then a.child_2
when 3 then a.child_3 end as child
  from Personnel a,
       (select 1 as Cnter from dual union all
        select 2          from dual union all
        select 3          from dual) b
order by a.employee,b.Cnter;

出力結果

employee	child
吉田	null
吉田	null
吉田	null
工藤	春子
工藤	夏子
工藤	null
赤井	一郎
赤井	二郎
赤井	三郎
鈴木	夏子
鈴木	null
鈴木	null

sys.odciVarchar2ListによるUnPivotの代用

　sys.odciVarchar2ListもUnPivotの代用法として使えます。ただし、sys.odciVarchar2Listを使うUnPivotでは、元々どの列の値だったかが分からなくなります。例えば、各child列が元々は、child_1,child_2,child_3のどれだったかが分からないですよね。そのため、order by句でソート順序を明示できなくなってしまいます。

sys.odciVarchar2ListによるUnPivotの代用

select employee,column_value as child
  from Personnel,table(sys.odciVarchar2List(child_1,child_2,child_3))
order by employee,child;

出力結果

employee	child
吉田	null
吉田	null
吉田	null
工藤	夏子
工藤	春子
工藤	null
赤井	一郎
赤井	三郎
赤井	二郎
鈴木	夏子
鈴木	null
鈴木	null