10 UnPivotのサンプル集

UnPivotの雛形

-- 基本的なUnPivot その1
with t(ID,Val1,Val2) as(
select 5,10,90 from dual union all
select 6,20,80 from dual union all
select 7,30,70 from dual)
select * from t
UnPivot(Vals for Cols in(Val1,Val2));

出力結果
ID  Cols  Vals
--  ----  ----
 5  VAL1    10
 5  VAL2    90
 6  VAL1    20
 6  VAL2    80
 7  VAL1    30
 7  VAL2    70

-- 基本的なUnPivot その2
with t(ID,Val1,Val2) as(
select 5,10,90 from dual union all
select 6,20,80 from dual union all
select 7,30,70 from dual)
select * from t
UnPivot(Vals for SortKeys
        in(Val1 as 1,
           Val2 as 2))
order by ID,SortKeys;

出力結果
ID  SortKeys  Vals
--  --------  ----
 5         1    10
 5         2    90
 6         1    20
 6         2    80
 7         1    30
 7         2    70

-- 基本的なUnPivot その3
with t(ID,Val1,Val2) as(
select 5,10,90 from dual union all
select 6,20,80 from dual union all
select 7,30,70 from dual)
select * from t
UnPivot(Vals for (SortKeys,Moto)
        in(Val1 as (1,'Moto1'),
           Val2 as (2,'Moto2')))
order by ID,SortKeys;

出力結果
ID  SortKeys  Moto   Vals
--  --------  -----  ----
 5         1  Moto1    10
 5         2  Moto2    90
 6         1  Moto1    20
 6         2  Moto2    80
 7         1  Moto1    30
 7         2  Moto2    70

-- 複数列でUnPivot その1
with t(ID,Val1,Name1,Val2,Name2) as(
select 1,11,'AA',66,'FF' from dual union all
select 2,22,'BB',77,'GG' from dual union all
select 3,33,'CC',88,'HH' from dual union all
select 4,44,'DD',99,'II' from dual union all
select 5,55,'EE',20,'JJ' from dual)
select * from t
UnPivot((Vals,Names) for Cols
        in((Val1,Name1) as 'V1N1',
           (Val2,Name2) as 'V2N2'));

出力結果
ID  Cols  Vals  Names
--  ----  ----  -----
 1  V1N1    11  AA
 1  V2N2    66  FF
 2  V1N1    22  BB
 2  V2N2    77  GG
 3  V1N1    33  CC
 3  V2N2    88  HH
 4  V1N1    44  DD
 4  V2N2    99  II
 5  V1N1    55  EE
 5  V2N2    20  JJ

-- 複数列でUnPivot その2
with t(ID,Val1,Name1,Val2,Name2) as(
select 1,11,'AA',66,'FF' from dual union all
select 2,22,'BB',77,'GG' from dual union all
select 3,33,'CC',88,'HH' from dual union all
select 4,44,'DD',99,'II' from dual union all
select 5,55,'EE',20,'JJ' from dual)
select * from t
UnPivot((Vals,Names) for (Col1,Col2)
        in((Val1,Name1) as ('V1','N1'),
           (Val2,Name2) as ('V2','N2')));

出力結果
ID  Col1  Col2  Vals  Names
--  ----  ----  ----  -----
 1  V1    N1      11  AA
 1  V2    N2      66  FF
 2  V1    N1      22  BB
 2  V2    N2      77  GG
 3  V1    N1      33  CC
 3  V2    N2      88  HH
 4  V1    N1      44  DD
 4  V2    N2      99  II
 5  V1    N1      55  EE
 5  V2    N2      20  JJ

-- UnPivotしてPivot
with t(ID,Val1,Val2,Val3,Val4) as(
select 'Sat',10,15,20,25 from dual union all
select 'Sun',30,35,40,45 from dual union all
select 'Mon',50,55,60,65 from dual)
select * from t
UnPivot(Vals for Cols in(Val1,Val2,Val3,Val4))
Pivot (max(Vals) for ID in('Sat' as Sat,'Sun' as Sun,'Mon' as Mon))
order by Cols;

出力結果
Cols  Sat  Sun  Mon
----  ---  ---  ---
VAL1   10   30   50
VAL2   15   35   55
VAL3   20   40   60
VAL4   25   45   65

▲ ページTOPに戻る

参考リソース

マニュアル --- PivotおよびUnPivotの使用例
マニュアル --- pivot_clauseとunpivot_clause
マニュアル --- ピボット操作

OracleSQLパズル 8-21 sys.odciVarchar2Listとsys.odciNumberList
OracleSQLパズル 10-278 unpivotとpivot
OracleSQLパズル 10-284 UnPivotして連番付与

"図でイメージするOracle DatabaseのSQL全集" インデックスに戻る

山岸 賢治（やまぎし けんじ）
Oracle ACEの1人。
OracleSQLパズルの運営者。

• ＜ 前のページへ
• 1
• 2
• 3
• 4
• 5