特集記事

Oracle 11g R1新機能のPivotとUnPivot

select文で縦横変換

山岸賢治[著]

2010/04/14 14:00

ポスト

ダウンロード SourceCode (2.6 KB)

4. PivotとUnPivotのサンプル集

　雛形にできる。「習うより慣れろ」なサンプル集です。

基本的なPivot

SQL文

with t as(
select 1111 key,1 seq,11 val from dual union all
select 1111    ,2    ,22     from dual union all
select 1111    ,4    ,33     from dual union all
select 2222    ,2    ,44     from dual union all
select 2222    ,4    ,55     from dual union all
select 3333    ,1    ,66     from dual union all
select 3333    ,3    ,77     from dual union all
select 3333    ,5    ,88     from dual)
select key,seq1,seq2,seq3,seq4,seq5
  from t
 pivot (max(val) for seq in (1 as seq1,
                             2 as seq2,
                             3 as seq3,
                             4 as seq4,
                             5 as seq5))
order by key;

出力結果

key	seq1	seq2	seq3	seq4	seq5
1111	11	22	null	33	null
2222	null	44	null	55	null
3333	66	null	77	null	88

複数列でPivot その1

SQL文

with t as(
select 1 ID,2009 year,1 month, 10 Val from dual union all
select 1   ,2009     ,2      , 20     from dual union all
select 1   ,2009     ,2      , 60     from dual union all
select 1   ,2009     ,2      ,100     from dual union all
select 1   ,2009     ,3      ,200     from dual union all
select 1   ,2009     ,3      ,600     from dual union all
select 2   ,2009     ,1      ,200     from dual union all
select 2   ,2009     ,3      ,300     from dual union all
select 2   ,2009     ,3      ,400     from dual)
select *
  from t pivot(max(Val)
               for (year,month)
               in ((2009,1) as N1,
                   (2009,2) as N2,
                   (2009,3) as N3));

出力結果

ID	N1	N2	N3
1	10	100	600
2	200	null	400

複数列でPivot その2

SQL文

with t as(
select 1 ID,2009 year,1 month, 10 Val from dual union all
select 1   ,2009     ,2      , 20     from dual union all
select 1   ,2009     ,2      , 60     from dual union all
select 1   ,2009     ,2      ,100     from dual union all
select 1   ,2009     ,3      ,200     from dual union all
select 1   ,2009     ,3      ,600     from dual union all
select 2   ,2009     ,1      ,200     from dual union all
select 2   ,2009     ,3      ,300     from dual union all
select 2   ,2009     ,3      ,400     from dual)
select *
  from t pivot(count(*) as cnt,
               max(Val) as maxVal
               for (year,month)
               in ((2009,1) as N1,
                   (2009,2) as N2,
                   (2009,3) as N3));

出力結果

ID	N1_CNT	N1_MAXVAL	N2_CNT	N2_MAXVAL	N3_CNT	N3_MAXVAL
1	1	10	3	100	2	600
2	1	200	0	null	2	400

基本的なUnPivot その1

SQL文

with t as(
select 1 ID,100 ValA,150 ValB from dual union
select 2   ,200     ,250      from dual union
select 3   ,300     ,350      from dual)
select * from t
unpivot(vals for SortKeys in(ValA as 123,ValB as 345))
order by ID,SortKeys;

出力結果

ID	SortKeys	vals
1	123	100
1	345	150
2	123	200
2	345	250
3	123	300
3	345	350

基本的なUnPivot その2

SQL文

with t as(
select 1 ID,100 ValA,150 ValB from dual union
select 2   ,200     ,250      from dual union
select 3   ,300     ,350      from dual)
select * from t
unpivot(vals for (SortKeys,moto)
        in (ValA as (123,'moto1'),
            ValB as (345,'moto2')))
order by ID,SortKeys;

出力結果

ID	SortKeys	moto	vals
1	123	moto1	100
1	345	moto2	150
2	123	moto1	200
2	345	moto2	250
3	123	moto1	300
3	345	moto2	350

複数列でUnPivot その1

SQL文

with t as(
select 1 ID,111 Val1,'AAAA' Name1,222 Val2,'FFFF' Name2 from dual union
select 1   ,333     ,'BBBB'      ,444,     'GGGG'       from dual union
select 1   ,555     ,'CCCC'      ,666,     'HHHH'       from dual union
select 2   ,777     ,'DDDD'      ,888,     'IIII'       from dual union
select 2   ,999     ,'EEEE'      ,200,     'JJJJ'       from dual)
select *
  from t UnPivot((Vals,Names) for Keys in((Val1,Name1) as 'V1N1',
                                          (Val2,Name2) as 'V2N2'));

出力結果

ID	Keys	Vals	Names
1	V1N1	111	AAAA
1	V2N2	222	FFFF
1	V1N1	333	BBBB
1	V2N2	444	GGGG
1	V1N1	555	CCCC
1	V2N2	666	HHHH
2	V1N1	777	DDDD
2	V2N2	888	IIII
2	V1N1	999	EEEE
2	V2N2	200	JJJJ

複数列でUnPivot その2

SQL文

with t as(
select 1 ID,111 Val1,'AAAA' Name1,222 Val2,'FFFF' Name2 from dual union
select 1   ,333     ,'BBBB'      ,444,     'GGGG'       from dual union
select 1   ,555     ,'CCCC'      ,666,     'HHHH'       from dual union
select 2   ,777     ,'DDDD'      ,888,     'IIII'       from dual union
select 2   ,999     ,'EEEE'      ,200,     'JJJJ'       from dual)
select *
  from t UnPivot((Vals,Names) for (Key1,Key2) in((Val1,Name1) as ('V1','N1'),
                                                 (Val2,Name2) as ('V2','N2')));

出力結果

ID	Key1	Key2	Vals	Names
1	V1	N1	111	AAAA
1	V2	N2	222	FFFF
1	V1	N1	333	BBBB
1	V2	N2	444	GGGG
1	V1	N1	555	CCCC
1	V2	N2	666	HHHH
2	V1	N1	777	DDDD
2	V2	N2	888	IIII
2	V1	N1	999	EEEE
2	V2	N2	200	JJJJ

UnPivotしてPivot

SQL文

with t as(
select 'sat' key,30 Val1,50 Val2,75 Val3,80 Val4 from dual union
select 'sun'    ,25     ,67     ,56     , 3      from dual union
select 'mon'    ,99     ,10     ,34     ,67      from dual)
select * from t
unpivot(vals for ValName in(Val1,Val2,Val3,Val4))
pivot (max(VALS) for KEY in('sat' as sat,'sun' as sun,'mon' as mon))
order by ValName;

出力結果

ValName	sat	sun	mon
VAL1	30	25	99
VAL2	50	67	10
VAL3	75	56	34
VAL4	80	3	67

最後に

　select文で縦横変換する際に多用されるPivotとUnPivotについて解説しました。機会があったら使ってみるといいでしょう。

参考資料

『PivotおよびUnPivotの使用例』
Oracleの公式マニュアルのPivotおよびUnPivotに関する説明です。
『pivot_clauseとunpivot_clause』
Oracleの公式マニュアルのPivotおよびUnPivotに関する説明です。
『ピボット操作』
Oracleの公式マニュアルのPivotおよびUnPivotに関する説明です。
『OracleSQLパズル 8-21 sys.ODCIVarchar2Listとsys.ODCINumberList』
sys.ODCIVarchar2Listとsys.ODCINumberListのサンプルです。