-- 基本的なPivot その1
with t(ID,Seq,Val) as(
select 111,1,77 from dual union all
select 111,2,66 from dual union all
select 111,3,55 from dual union all
select 222,1,44 from dual union all
select 222,3,33 from dual union all
select 333,2,22 from dual)
select * from t
Pivot(max(Val) for Seq in(1,2,3))
order by ID;
-- 基本的なPivot その2
with t(ID,Seq,Val) as(
select 111,1,77 from dual union all
select 111,2,66 from dual union all
select 111,3,55 from dual union all
select 222,1,44 from dual union all
select 222,3,33 from dual union all
select 333,2,22 from dual)
select * from t
Pivot(max(Val) for Seq in(1 as Seq1,
2 as Seq2,
3 as Seq3))
order by ID;
-- 複数列でPivot その1
with t(ID,Year,Month,Val) as(
select 1,2012,1, 10 from dual union all
select 1,2012,2, 20 from dual union all
select 1,2012,3, 60 from dual union all
select 2,2012,1,300 from dual union all
select 2,2012,3,500 from dual union all
select 3,2012,2,900 from dual)
select * from t
Pivot(max(Val)
for (Year,Month)
in ((2012,1) as Agg1,
(2012,2) as Agg2,
(2012,3) as Agg3));
-- 複数列でPivot その2
with t(ID,Year,Month,Val) as(
select 1,2012,1, 10 from dual union all
select 1,2012,1,700 from dual union all
select 1,2012,2, 20 from dual union all
select 1,2012,3, 60 from dual union all
select 2,2012,1,300 from dual union all
select 2,2012,1,999 from dual union all
select 2,2012,3,500 from dual union all
select 3,2012,2,900 from dual)
select * from t
Pivot(count(*) as cnt,
max(Val) as max
for (Year,Month)
in ((2012,1) as Agg1,
(2012,2) as Agg2,
(2012,3) as Agg3));