model句05 pivotをあえてmodel句で

SQLパズル

modelPivot
ID  seq  Val
--  ---  -----
10  01   AAAAA
10  02   BBBBB
10  03   CCCCC
20  01   DDDDD
20  02   EEEEE

下記のpivotを、あえてmodel句を使って行う。

出力結果
ID  Val01  Val02  Val03
--  -----  -----  -----
10  AAAAA  BBBBB  CCCCC
20  DDDDD  EEEEE  null

データ作成スクリプト

create table modelPivot(ID,seq,Val) as
select 10,'01','AAAAA' from dual union all
select 10,'02','BBBBB' from dual union all
select 10,'03','CCCCC' from dual union all
select 20,'01','DDDDD' from dual union all
select 20,'02','EEEEE' from dual;

SQL

--■■■model句を使う方法■■■
select ID,Val01,Val02,Val03
  from modelPivot
 model return updated rows
 PARTITION BY (ID)
 DIMENSION BY (seq)
 measures(Val as Val01,Val as Val02,Val as Val03)
 rules(Val01['01'] = Val01['01'],
       Val02['01'] = Val02['02'],
       Val03['01'] = Val03['03'])
order by ID;

--■■■一般的な方法■■■
select ID,
max(decode(seq,'01',Val)) as Val01,
max(decode(seq,'02',Val)) as Val02,
max(decode(seq,'03',Val)) as Val03
  from modelPivot
group by ID
order by ID;

解説

model句でのpartition byの使い方の、いいサンプルだと思います。

---