CONNECT BY…… START WITH
CREATE TABLE sys_cbp_test2 (id INTEGER NOT NULL PRIMARY KEY,parent_id INTEGER );
INSERT INTO sys_cbp_test2
SELECT 1, NULL FROM dual
UNION ALL SELECT 2, 1 FROM dual
UNION ALL SELECT 3, 2 FROM dual
UNION ALL SELECT 4, 3 FROM dual
UNION ALL SELECT 5, 1 FROM dual
UNION ALL SELECT 6, 5 FROM dual
UNION ALL SELECT 7, 2 FROM dual
UNION ALL SELECT 20, NULL FROM dual
UNION ALL SELECT 21, 20 FROM dual
UNION ALL SELECT 22, 21 FROM dual;
vastbase=# SELECT id, parent_id,sys_connect_by_path (TO_CHAR (id), '/') AS Path
vastbase-# FROM sys_cbp_test2
vastbase-# START WITH parent_id IS NULL CONNECT BY prior id = parent_id;
id | parent_id | path
----+-----------+-----------
1 | | /1
20 | | /20
2 | 1 | /1/2
5 | 1 | /1/5
21 | 20 | /20/21
3 | 2 | /1/2/3
6 | 5 | /1/5/6
7 | 2 | /1/2/7
22 | 21 | /20/21/22
4 | 3 | /1/2/3/4
(10 rows)
