でイメージするOracle DatabaseのSQL全集第8回 PivotとUnPivot

7 UnPivotの使い方

列持ちデータを行持ちデータに変換



create table UnPivotSample(
ID   number(1) primary key,
Val1 number(2),
Val2 number(2),
Val3 number(2));

insert into UnPivotSample
select 1,12,  11,  10 from dual union all
select 3,30,  90,null from dual union all
select 5,50,null,null from dual;

UnPivotを使って、列持ちデータを行持ちデータに変換してみます。

Copy



-- UnPivotのサンプル
select ID,Vals,Cols
  from UnPivotSample
UnPivot(Vals for Cols in(Val1,Val2,Val3));

Copy



出力結果
ID  Vals  Cols
--  ----  ----
 1    12  VAL1
 1    11  VAL2
 1    10  VAL3
 3    30  VAL1
 3    90  VAL2
 5    50  VAL1

上記の出力結果では、Valsがnullの行が出力されてないですね。 UnPivotは、デフォルトでExclude nullsなため、 UnPivot対象がnullの行も出力するには、Include nullsを指定する必要があります。

Copy



-- Include nullsを指定したUnPivot
select ID,Vals,Cols
  from UnPivotSample
UnPivot Include nulls
(Vals for Cols in(Val1,Val2,Val3));

Copy



出力結果
ID  Vals  Cols
--  ----  ----
 1    12  VAL1
 1    11  VAL2
 1    10  VAL3
 3    30  VAL1
 3    90  VAL2
 3  null  VAL3
 5    50  VAL1
 5  null  VAL2
 5  null  VAL3

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

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

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

下記のように、元列の識別値を指定することもできます。 Val1列の元列の識別値をMoto1,Val2列の元列の識別値をMoto2,Val3列の元列の識別値をMoto3としてみます。

Copy



-- 元列の識別値を指定
select ID,Vals,Cols
  from UnPivotSample
UnPivot(Vals for Cols in(Val1 as 'Moto1',
                         Val2 as 'Moto2',
                         Val3 as 'Moto3'));

Copy



出力結果
ID  Vals  Cols
--  ----  -----
 1    12  Moto1
 1    11  Moto2
 1    10  Moto3
 3    30  Moto1
 3    90  Moto2
 5    50  Moto1

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

Copy



-- 数値型のソートキーを持たせたUnPivot
select ID,Vals,SortKeys
  from UnPivotSample
UnPivot(Vals for SortKeys in(Val1 as 1,
                             Val2 as 2,
                             Val3 as 3))
order by ID,SortKeys;

Copy



出力結果
ID  Vals  SortKeys
--  ----  --------
 1    12         1
 1    11         2
 1    10         3
 3    30         1
 3    90         2
 5    50         1

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

Copy



-- 元列の識別値として数値型と列名の両方を指定
select ID,Vals,SortKeys,Moto
  from UnPivotSample
UnPivot (Vals for (SortKeys,Moto)
         in(Val1 as (1,'Moto1'),
            Val2 as (2,'Moto2'),
            Val3 as (3,'Moto3')))
order by ID,SortKeys;

Copy



出力結果
ID  Vals  SortKeys  Moto
--  ----  --------  -----
 1    12         1  Moto1
 1    11         2  Moto2
 1    10         3  Moto3
 3    30         1  Moto1
 3    90         2  Moto2
 5    50         1  Moto1

▲ ページTOPに戻る

8 UnPivotの代用法

union allなどでUnPivotを代用

UnPivotの代用法としては、下記のようにunion allを使う方法があります。

Copy



-- UnPivotの代用 (union all)
with tmp(ID,Vals,SortKeys) as(
select ID,Val1,1 from UnPivotSample union all
select ID,Val2,2 from UnPivotSample union all
select ID,Val3,3 from UnPivotSample)
select ID,Vals,SortKeys
  from tmp
order by ID,SortKeys;

Copy



出力結果
ID  Vals  SortKeys
--  ----  --------
 1    12         1
 1    11         2
 1    10         3
 3    30         1
 3    90         2
 3  null         3
 5    50         1
 5  null         2
 5  null         3

下記のように連番表とクロスジョインさせる方法も、UnPivotの代用法として使えます。

Copy



-- UnPivotの代用 (連番表とクロスジョイン)
select a.ID,
case b.Cnter
when 1 then a.Val1
when 2 then a.Val2
when 3 then a.Val3 end as Vals,b.Cnter
  from UnPivotSample a,
       (select 1 as Cnter from dual union all
        select 2          from dual union all
        select 3          from dual) b
order by a.ID,b.Cnter;

Copy



出力結果
ID  Vals  Cnter
--  ----  -----
 1    12      1
 1    11      2
 1    10      3
 3    30      1
 3    90      2
 3  null      3
 5    50      1
 5  null      2
 5  null      3

下記のようにsys.odciNumberListも、UnPivotの代用法として使えます。ただし、この代用法では、元々どの列の値だったかが分からなくなります。例えば、下記のselect文では、各Vals列が元々は、Val1列,Val2列,Val3列のどれだったかが分からないです。そのため、order by句でソート順序を明示できなくなってしまいます。

Copy


-- UnPivotの代用 (sys.odciNumberList)
select ID,column_value as Vals
  from UnPivotSample,table(sys.odciNumberList(Val1,Val2,Val3));

Copy


出力結果
ID  Vals
--  ----
 1    12
 1    11
 1    10
 3    30
 3    90
 3  null
 5    50
 5  null
 5  null

▲ ページTOPに戻る

9 UnPivotと、UnPivotの代用法を比較

UnPivotの構文はシンプル

UnPivotと、UnPivotの代用法の比較結果として、 UnPivotの構文はシンプルで読みやすいので、UnPivotが使えるOracle11gR1以降では、UnPivotを使い、 UnPivotが使えないOracle10gなどでは、前述したUnPivotの代用法を使い分けるのがいいと思われます。

▲ ページTOPに戻る