再帰with句02 階層問い合わせのsys_connect_by_path関数を模倣

SQLパズル

IDTable
ID  OyaID
--  -----
 1   null
 2      1
 3      2
 4      3
 5      1
 6      5
 7      2
20   null
21     20
22     21

階層問い合わせを使った下記のクエリと同じ結果を取得する。

select ID,OyaID,Level,sys_connect_by_path(to_char(ID),',') as Path
  from IDTable
start with OyaID is null
connect by prior ID = OyaID;

出力結果
ID  OyaID  Level  Path
--  -----  -----  ---------
 1   null      1  ,1
 2      1      2  ,1,2
 3      2      3  ,1,2,3
 4      3      4  ,1,2,3,4
 7      2      3  ,1,2,7
 5      1      2  ,1,5
 6      5      3  ,1,5,6
20   null      1  ,20
21     20      2  ,20,21
22     21      3  ,20,21,22

データ作成スクリプト

create table IDTable(
ID    number primary key,
OyaID number);

insert into IDTable values( 1,null);
insert into IDTable values( 2,   1);
insert into IDTable values( 3,   2);
insert into IDTable values( 4,   3);
insert into IDTable values( 5,   1);
insert into IDTable values( 6,   5);
insert into IDTable values( 7,   2);
insert into IDTable values(20,null);
insert into IDTable values(21,  20);
insert into IDTable values(22,  21);
commit;

SQL

col path for a10

with rec(ID,OyaID,LV,path) as(
select ID,OyaID,1,',' || to_char(ID)
  from IDTable
 where OyaID is null
union all
select b.ID,b.OyaID,a.LV+1,a.path || ',' || to_char(b.ID)
  from rec a,IDTable b
 where a.ID = b.OyaID)
search depth first by ID set SortKey
select * from rec
order by SortKey;

解説

|| で文字列を連結させていってます。