WITH T(ID, PC방이름, 상위PC방ID) AS ( SELECT '1', 'PC방1', NULL FROM DUAL UNION ALL SELECT '2', 'PC방2', '1' FROM DUAL UNION ALL SELECT '3', 'PC방3', '2' FROM DUAL UNION ALL SELECT '4', 'PC방4', '1' FROM DUAL UNION ALL SELECT '5', 'PC방5', '4' FROM DUAL UNION ALL SELECT '6', 'PC방6', NULL FROM DUAL UNION ALL SELECT '7', 'PC방7', '6' FROM DUAL UNION ALL SELECT '8', 'PC방8', '7' FROM DUAL ) SELECT SUBSTR(SYS_CONNECT_BY_PATH(PC방이름, ' '), 2) path FROM T WHERE CONNECT_BY_ISLEAF = 1 START WITH 상위PC방ID IS NULL CONNECT BY PRIOR ID = 상위PC방ID -- http://www.gurubee.net/lecture/1903 강좌 참고하시면 잘 나와있습니다.
-- 3단계 계층이라고 치면.. SELECT path , REGEXP_SUBSTR(path, '[^ ]+', 1, 1) col1 , REGEXP_SUBSTR(path, '[^ ]+', 1, 2) col2 , REGEXP_SUBSTR(path, '[^ ]+', 1, 3) col3 FROM ( SELECT SUBSTR(SYS_CONNECT_BY_PATH(PC방이름, ' '), 2) path FROM T WHERE CONNECT_BY_ISLEAF = 1 START WITH 상위PC방ID IS NULL CONNECT BY PRIOR ID = 상위PC방ID )