약간 억지성으로 작성해봤습니다.. 수정이 필요할거 같긴 한데 초보라...

WITH TMP AS(
SELECT 'AA10' AS MENUCODE, '수주지원' AS MENUNAME FROM DUAL UNION ALL
SELECT 'AA1005', '설계(관리)' FROM DUAL UNION ALL
SELECT 'AA1010', 'VE' FROM DUAL UNION ALL
SELECT 'AA1025', 'BIM' FROM DUAL UNION ALL
SELECT 'AA102505', 'MODELING' FROM DUAL
)
SELECT AA
, BB
, MAX(CC)
FROM (
SELECT CASE
WHEN LEVEL = 1 THEN MENUNAME
ELSE REGEXP_SUBSTR(SYS_CONNECT_BY_PATH( MENUNAME, '|' ), '[^|]*', 1, 2)
END AS AA,
CASE
WHEN LEVEL = 1 THEN REGEXP_SUBSTR(SYS_CONNECT_BY_PATH( MENUNAME, '|' ), '[^|]*', 1, LEVEL+1)
WHEN LEVEL = 2 THEN MENUNAME
WHEN LEVEL = 3 THEN REGEXP_SUBSTR(SYS_CONNECT_BY_PATH( MENUNAME, '|' ), '[^|]*', 1, LEVEL+1)
END AS BB,
CASE
WHEN LEVEL = 1 THEN REGEXP_SUBSTR(SYS_CONNECT_BY_PATH( MENUNAME, '|' ), '[^|]*', 1, LEVEL)
WHEN LEVEL = 2 THEN REGEXP_SUBSTR(SYS_CONNECT_BY_PATH( MENUNAME, '|' ), '[^|]*', 1, LEVEL+2)
WHEN LEVEL = 3 THEN MENUNAME
END AS CC
FROM (SELECT MENUCODE
, MENUNAME
, CASE
WHEN LENGTH(MENUCODE) > 4 THEN SUBSTR(MENUCODE,1,LENGTH(MENUCODE)-2)
ELSE ''
END AS UP_MENUCODE
FROM TMP) A
WHERE LEVEL >= 2
START WITH MENUCODE = 'AA10'
CONNECT BY PRIOR MENUCODE = UP_MENUCODE
) GROUP BY AA,BB