WITH TEST AS (SELECT 'A' ID, 'A1' PID FROM DUAL UNION ALL SELECT 'A' ID, 'A2' PID FROM DUAL UNION ALL SELECT 'A1' ID, 'A11' PID FROM DUAL UNION ALL SELECT 'A1' ID, 'A12' PID FROM DUAL UNION ALL SELECT 'A11' ID, 'A111' PID FROM DUAL UNION ALL SELECT 'A2' ID, 'A21' PID FROM DUAL UNION ALL SELECT 'A2' ID, 'A22' PID FROM DUAL) SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(RN, '-')), '-') COL, T2.ID, T2.PID FROM (SELECT T1.*, ROW_NUMBER() OVER(PARTITION BY C_LEVEL, ID ORDER BY PID) AS RN FROM (SELECT T.ID, T.PID, MAX(LEVEL) C_LEVEL FROM TEST T CONNECT BY ID = PRIOR PID GROUP BY T.ID, T.PID) T1) T2 START WITH C_LEVEL = 1 CONNECT BY ID = PRIOR PID GROUP BY T2.ID, T2.PID, T2.RN ORDER BY LTRIM(MAX(SYS_CONNECT_BY_PATH(RN, '-')), '-') ;
SQL> WITH TEST AS (SELECT 'A' ID, 'A1' PID FROM DUAL 2 UNION ALL SELECT 'A' ID, 'A2' PID FROM DUAL 3 UNION ALL SELECT 'A1' ID, 'A11' PID FROM DUAL 4 UNION ALL SELECT 'A1' ID, 'A12' PID FROM DUAL 5 UNION ALL SELECT 'A11' ID, 'A111' PID FROM DUAL 6 UNION ALL SELECT 'A2' ID, 'A21' PID FROM DUAL 7 UNION ALL SELECT 'A2' ID, 'A22' PID FROM DUAL) 8 select * from test start with id='A' connect by prior pid=id;ID PID --- ---- A A1 A1 A11 A11 A111 A1 A12 A A2 A2 A21 A2 A22
(SELECT 'A' ID, 'A1' PID
FROM DUAL
UNION ALL
SELECT 'A' ID, 'A2' PID
FROM DUAL
UNION ALL
SELECT 'A1' ID, 'A11' PID
FROM DUAL
UNION ALL
SELECT 'A1' ID, 'A12' PID
FROM DUAL
UNION ALL
SELECT 'A11' ID, 'A111' PID
FROM DUAL
UNION ALL
SELECT 'A2' ID, 'A21' PID
FROM DUAL
UNION ALL
SELECT 'A2' ID, 'A22' PID
FROM DUAL)
SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(RN, '-')), '-') COL, T2.ID, T2.PID
FROM (SELECT T1.*,
ROW_NUMBER() OVER(PARTITION BY C_LEVEL, ID ORDER BY PID) AS RN
FROM (SELECT T.ID, T.PID, MAX(LEVEL) C_LEVEL
FROM TEST T
CONNECT BY ID = PRIOR PID
GROUP BY T.ID, T.PID) T1) T2
START WITH C_LEVEL = 1
CONNECT BY ID = PRIOR PID
GROUP BY T2.ID, T2.PID, T2.RN
ORDER BY LTRIM(MAX(SYS_CONNECT_BY_PATH(RN, '-')), '-')
;
2 UNION ALL SELECT 'A' ID, 'A2' PID FROM DUAL
3 UNION ALL SELECT 'A1' ID, 'A11' PID FROM DUAL
4 UNION ALL SELECT 'A1' ID, 'A12' PID FROM DUAL
5 UNION ALL SELECT 'A11' ID, 'A111' PID FROM DUAL
6 UNION ALL SELECT 'A2' ID, 'A21' PID FROM DUAL
7 UNION ALL SELECT 'A2' ID, 'A22' PID FROM DUAL)
8 select * from test start with id='A' connect by prior pid=id;ID PID
--- ----
A A1
A1 A11
A11 A111
A1 A12
A A2
A2 A21
A2 A22