--■■■unpivotとpivotを使う方法(11g以降)■■■
select * from DayValTable
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;
--■■■model句を使う方法(10g以降)■■■
select Val1 as sat,Val2 as sun,Val3 as mon
from DayValTable
model RETURN UPDATED ROWS
dimension by(key)
measures(Val1,Val2,Val3,Val4)
rules(
Val1['A'] = Val1['sat'], Val2['A'] = Val1['sun'], Val3['A'] = Val1['mon'],
Val1['B'] = Val2['sat'], Val2['B'] = Val2['sun'], Val3['B'] = Val2['mon'],
Val1['C'] = Val3['sat'], Val2['C'] = Val3['sun'], Val3['C'] = Val3['mon'],
Val1['D'] = Val4['sat'], Val2['D'] = Val4['sun'], Val3['D'] = Val4['mon'])
order by key;
--■■■内部結合とcase式を使う方法■■■
select max(decode(key,'sat',Val)) as sat,
max(decode(key,'sun',Val)) as sun,
max(decode(key,'mon',Val)) as mon
from (select a.key,b.column_Value,
case b.column_Value when 1 then a.Val1
when 2 then a.Val2
when 3 then a.Val3
when 4 then a.Val4 end as Val
from DayValTable a,table(sys.odciNumberList(1,2,3,4)) b)
group by column_Value
order by column_Value;
Oracle11gR1の新機能である、unpivotとpivotを使うと、縦横変換が容易にできます。
構文は、下記のように理解するといいかもしれません。
UnPivot(列値を表示する列名 for 元列の識別値を表示する列名 in(元列1,元列2,元列3))
Pivot(集約関数 for 集約条件列 in(集約条件値1 as 集約後列名1,
集約条件値2 as 集約後列名2,
集約条件値3 as 集約後列名3))
マニュアル --- PIVOTおよびUNPIVOTの使用例:
マニュアル --- ピボット操作
Laurent Schneider --- pivot table
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
UnPivotのサンプル
-- 基本的なUnPivot1
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 Cols in(ValA,ValB))
order by ID;
出力結果
ID Cols Vals
-- ---- ----
1 VALA 100
1 VALB 150
2 VALA 200
2 VALB 250
3 VALA 300
3 VALB 350
-- 基本的なUnPivot2
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
-- 基本的なUnPivot3
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
-- 複数列でUnPivot1
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
-- 複数列でUnPivot2
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
10-284 UnPivotして連番付与
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Pivotのサンプル
-- 基本的なPivot
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
-- 複数列でPivot1
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
-- 複数列でPivot2
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