10-25 1行を複数行に変換

SQLパズル

tab1テーブル
FLD1
---------------
101/102/103
105/102/109/108
110/111/112
9990/9993

tab1テーブルのデータを、出力結果のように出力する。

出力結果
FLD1
----
 101
 102
 103
 105
 102
 109
 108
 110
 111
 112
9990
9993

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

データ作成スクリプト

create table tab1(FLD1 char(15));

insert into tab1 values('101/102/103');
insert into tab1 values('105/102/109/108');
insert into tab1 values('110/111/112');
insert into tab1 values('9990/9993');
commit;

SQL

col FLD1 for a10

--■■■instr関数とsubstr関数を使う方法■■■
select
substr(
a.FLD1,
decode(b.Ord,1,1,instr(a.FLD1,'/',1,b.Ord-1)+1),
case when instr(a.FLD1,'/',decode(b.Ord,1,1,instr(a.FLD1,'/',1,b.Ord-1)+1)) > 0
     then instr(a.FLD1,'/',decode(b.Ord,1,1,instr(a.FLD1,'/',1,b.Ord-1)+1))
         -decode(b.Ord,1,1,instr(a.FLD1,'/',1,b.Ord-1)+1)
     else Length(a.FLD1) end) as FLD1
from tab1 a,
(select RowNum as Ord from all_catalog
 where RowNum <= (select max(Length(FLD1)-Length(Replace(FLD1,'/'))+1) from tab1)) b
 where b.Ord <= Length(a.FLD1)-Length(Replace(FLD1,'/'))+1
order by a.FLD1,b.Ord;

--■■■正規表現を使う方法(10g以降)■■■
select
RegExp_Replace(a.FLD1,'^([^/]+/){' || to_char(b.Ord-1) || '}([^/]+).*$','\2') as FLD1
from tab1 a,
(select RowNum as Ord from all_catalog
 where RowNum <= (select max(Length(FLD1)-Length(Replace(FLD1,'/'))+1) from tab1)) b
 where b.Ord <= Length(a.FLD1)-Length(Replace(FLD1,'/'))+1
order by a.FLD1,b.Ord;

--■■■model句を使う方法(10g以降)■■■
select FLD1
  from tab1
 model
 partition by (RowNum as PID)
 dimension by (0 as soeji)
 measures (FLD1,FLD1 as BaseFLD1)
 rules ITERATE (1000) UNTIL (RegExp_Instr (BaseFLD1[0],'[^/]+',1,ITERATION_NUMBER+2) = 0)
 (FLD1[0+ITERATION_NUMBER] = RegExp_Substr(BaseFLD1[0],'[^/]+',1,ITERATION_NUMBER+1))
order by PID,soeji;

--■■■再帰with句を使う方法(11gR2以降)■■■
with rec(FLD1,subFLD1,LV,maxLV) as(
select FLD1,RegExp_Substr(FLD1,'[^/]+',1,1),1,
RegExp_Count(FLD1,'[^/]+')
  from tab1
union all
select FLD1,RegExp_Substr(FLD1,'[^/]+',1,LV+1),LV+1,maxLV
  from rec
 where LV < maxLV)
select*from rec order by FLD1,LV;

--■■■表関数を使う方法■■■
create or replace Package Pack10_25 Is
    type outType is table of varchar2(10);
end;
/

create or replace function FOutDate return Pack10_25.outType pipelined
is
    willOut varchar2(10);
begin
    for rec in (select FLD1
                  from tab1
                order by FLD1) Loop
        for I in 1..length(rec.FLD1) Loop
            if substr(rec.FLD1,I,1) = '/' then
                pipe row(willOut);
                willOut := null;
            else
                willOut := willOut || substr(rec.FLD1,I,1);
            end if;
       end Loop;

        pipe row(willOut);
        willOut := null;
    end Loop;
end;
/

create or replace view outView(FLD1) as
select * from table(FOutDate);

select FLD1 from outView;

解説

再帰with句かmodel句を使うのが一番シンプルでしょう。

インラインビューで、
1からスラッシュの最大数までの、自然数の連番を持つ集合を作成して、
結合させてます。

正規表現を使う方法では、後方参照を使ってます。



類似な問題(メールアドレスを複数行に変換)

with WorkView as (
select 'abc@xyz.com' as str from dual union
select 'cbd@hsfd.com'       from dual union
select 'ghf@ury.com'        from dual)
select newStr
  from WorkView
 model
 partition by (RowNum as PID)
 dimension by (0 as soeji)
 measures(str,str as newStr)
 rules iterate (3) (newStr[iteration_number]
                  = RegExp_Substr(str[0],'[^@.]+',1,iteration_number+1));
newStr
------
abc
xyz
com
cbd
hsfd
com
ghf
ury
com

---