6 Pivotのサンプル集

Pivotの雛形

-- 基本的な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;
出力結果
 ID     1     2     3
---  ----  ----  ----
111    77    66    55
222    44  null    33
333  null    22  null
-- 基本的な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;
出力結果
 ID  Seq1  Seq2  Seq3
---  ----  ----  ----
111    77    66    55
222    44  null    33
333  null    22  null
-- 複数列で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));
出力結果
ID  Agg1  Agg2  Agg3
--  ----  ----  ----
 1    10    20    60
 2   300  null   500
 3  null   900  null
-- 複数列で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));
出力結果
ID  AGG1_CNT  AGG1_MAX  AGG2_CNT  AGG2_MAX  AGG3_CNT  AGG3_MAX
--  --------  --------  --------  --------  --------  --------
 1         2       700         1        20         1        60
 2         2       999         0      null         1       500
 3         0      null         1       900         0      null

▲ ページTOPに戻る