WITH TESTTABLE AS (
SELECT '0' ID, 'TEST1' NAME,'' PID, 1 SORTBY FROM DUAL
UNION ALL
SELECT '1' ID, 'TEST1-1' NAME,'0' PID, 1 SORTBY FROM DUAL
UNION ALL
SELECT '2' ID, 'TEST1-2' NAME,'0' PID, 1 SORTBY FROM DUAL
UNION ALL
SELECT '3' ID, 'TEST1-3' NAME,'0' PID, 1 SORTBY FROM DUAL
UNION ALL
SELECT '11' ID, 'TEST1-1-1' NAME,'1' PID, 1 SORTBY FROM DUAL
UNION ALL
SELECT '12' ID, 'TEST1-1-2' NAME,'1' PID, 2 SORTBY FROM DUAL
UNION ALL
SELECT '13' ID, 'TEST1-1-3' NAME,'1' PID, 3 SORTBY FROM DUAL
)
SELECT ID,
(CASE
WHEN LEVEL < 2 THEN '<>'
ELSE LPAD (' ', LEVEL * 2, ' ') || '|- '
END)
|| NAME
NAME,
NAME DEPTITLE,
SUBSTR(SYS_CONNECT_BY_PATH(NAME, '>'), 2) NAME_PATH,
CONNECT_BY_ROOT(NAME) TOPDEP,
CONNECT_BY_ISLEAF ISLEAF,
LEVEL,
PID,
(
SELECT
TOPID
FROM
(
SELECT
F.ID,
CONNECT_BY_ROOT(F.ID) TOPID
FROM
TESTTABLE F
START WITH
F.PID = '0'
CONNECT BY
PRIOR F.ID = F.PID
)
WHERE
ID = A.ID
) TOPID,
(
SELECT
TOPNAME
FROM
(
SELECT
F.ID,
CONNECT_BY_ROOT(F.NAME) TOPNAME
FROM
TESTTABLE F
START WITH
F.PID = '0'
CONNECT BY
PRIOR F.ID = F.PID
)
WHERE
ID = A.ID
) TOPNAME
FROM TESTTABLE A
START WITH PID IS NULL
CONNECT BY PRIOR ID = PID
ORDER SIBLINGS BY SORTBY