10-284 UnPivotして連番付与

SQLパズル

UnPivT
seq  ID1  ID2  ID3  ID4  ID5
---  ---  ---  ---  ---  ---
  1  111  222  333  444  555
  2  666  777  888  999  123

ID1,ID2,ID3,ID4,ID5を
行に変換し連番を付与する。

出力結果
seq  ID
---  ---
  1  111
  2  222
  3  333
  4  444
  5  555
  6  666
  7  777
  8  888
  9  999
 10  123

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table UnPivT(seq,ID1,ID2,ID3,ID4,ID5) as
select 1,111,222,333,444,555 from dual union all
select 2,666,777,888,999,123 from dual;

SQL

--■■■UnPivotを使う方法(11g以降)■■■
select Row_Number() over(order by ID,SortKeys) as seq,ID
  from UnPivT
unpivot(ID for SortKeys in(ID1 as 1,ID2 as 2,ID3 as 3,ID4 as 4,ID5 as 5))
order by seq;

--■■■model句を使う方法(10g以降)■■■
select Row_Number() over(order by seq,soeji) as seq,ID1 as ID
from (select seq,soeji,ID1
        from UnPivT
       model
      partition by(seq)
      dimension by(1 as soeji)
      measures(ID1,ID2,ID3,ID4,ID5)
      rules(
      ID1[2] = ID2[1],
      ID1[3] = ID3[1],
      ID1[4] = ID4[1],
      ID1[5] = ID5[1]));

--■■■クロスジョインを使う方法■■■
select Row_Number() over(order by a.seq,b.Column_Value) as seq,
case b.Column_Value when 1 then a.ID1
                    when 2 then a.ID2
                    when 3 then a.ID3
                    when 4 then a.ID4
                    when 5 then a.ID5 end as ID
  from UnPivT a,table(sys.odciNumberList(1,2,3,4,5)) b
order by seq,b.Column_Value;

解説

UnPivotの使いどころといえるでしょう。

10-278 unpivotとpivot

---