SYS_CONNECT_BY_PATH

구문

SQL> select id,subid, Level, ltrim(sys_connect_by_path (to_char (id), ','), ',') as Path
from sys_cbp_test
start with subid is null
connect by prior id=subid;

구문

with recursive X (id, subid, ArrPath) as
(
select id, subid, array [id]
from sys_cbp_test
where subid is null
union all
select b.id, b.subid, X.ArrPath || b.id

from X, sys_cbp_test b
where X.id = b.subid
)
select id,subid,array_upper (ArrPath, 1) as Level, Array_to_string (ArrPath, ',') as path
from X
order by ArrPath;

결과값

 id | oyaid | level |   path
----+-------+-------+-----------
  1 |       |     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 |       |     1 | 20
 21 |    20 |     2 | 20,21
 22 |    21 |     3 | 20,21,22
(10 rows)