MSSQL 기준으로 작성된 쿼리입니다.

결과만 나오게 해뒀어요 -_-;;;

오라클에 맞게끔 구문 다른것들 수정해서

적용하시면 될듯합니다.

---쿼리시작---

;with tblA(depth1_id,depth2_id,depth3_id) As
(
   Select '01','0101','010101' Union All
   Select '01','0102','010201' Union All
   Select '02','0202',''
)
,tblB(level,depth1_id,depth2_id,depth3_id,serl) As
(
   Select 1         
         ,a.depth1_id
         ,''
         ,''
         ,a.depth1_id + a.depth2_id + a.depth3_id         
     From tblA a   
    Where a.depth1_id <> ''
     
 Union All
   
   Select 2
         ,''
         ,a.depth2_id
         ,''
         ,a.depth1_id + a.depth2_id + a.depth3_id
     From tblA a    
    Where a.depth2_id <> ''

 Union All
   
   Select 3
         ,''
         ,''
         ,a.depth3_id
         ,a.depth1_id + a.depth2_id + a.depth3_id
     From tblA a    
    Where a.depth3_id <> ''
)
   Select a.*
     From tblB a
 Order By a.serl Asc
         ,a.level Asc     
     

---쿼리끝---

그럼 수고하세용~

WITH TMP AS (
SELECT '01' AS D1, '0101' AS D2, '010101' AS D3 FROM DUAL UNION ALL
SELECT '01' AS D1, '0102' AS D2, '010201' AS D3 FROM DUAL UNION ALL
SELECT '02' AS D1, '0202' AS D2, '' AS D3 FROM DUAL 
)

SELECT DECODE(LV, 1, D1) AS D11
           , DECODE(LV, 2, D2) AS D22 
         , DECODE(LV, 3, D3) AS D33
FROM TMP T,
(SELECT LEVEL AS LV FROM DUAL 
 CONNECT BY LEVEL <= 3) D
WHERE (DECODE(LV, 1, D1)||DECODE(LV, 2, D2)||DECODE(LV, 3, D3)) IS NOT NULL
ORDER BY D1||D2||D3, LV

WITH t AS
(
SELECT '01' depth1_id, '0101' depth2_id, '010101' depth3_id FROM dual
UNION ALL SELECT '01', '0102', '010201' FROM dual
UNION ALL SELECT '02', '0202', '' FROM dual
UNION ALL SELECT '03', '', '' FROM dual
)

-- 1. 3레벨 복제하여 (코드, 상위코드) 형태로 추출 후 계층 구조 전개
SELECT lv
     , DECODE(lv, 1, depth_id) depth1_id
     , DECODE(lv, 2, depth_id) depth2_id
     , DECODE(lv, 3, depth_id) depth3_id
  FROM (SELECT DISTINCT lv
             , DECODE(lv, 1, depth1_id, 2, depth2_id, 3, depth3_id) depth_id
             , DECODE(lv, 1, ''       , 2, depth1_id, 3, depth2_id) p_depth_id
          FROM t
             , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 3)
         WHERE lv <= DECODE('', depth2_id, 1, depth3_id, 2, 3)
        )
 START WITH lv = 1
 CONNECT BY PRIOR depth_id = p_depth_id
 ORDER SIBLINGS BY depth_id
;

-- 2. 롤업을 이용해 상위코드 추출 후 정렬
SELECT lv
     , DECODE(lv, 1, depth1_id) depth1_id
     , DECODE(lv, 2, depth2_id) depth2_id
     , DECODE(lv, 3, depth3_id) depth3_id
  FROM (SELECT depth1_id, depth2_id, depth3_id
             , DECODE(GROUPING_ID(depth2_id, depth3_id), 3, 1, 1, 2, 0, 3) lv
          FROM t
         GROUP BY depth1_id, ROLLUP(depth2_id, depth3_id)
        HAVING NOT(depth2_id IS NULL AND GROUPING(depth2_id) = 0)
           AND NOT(depth3_id IS NULL AND GROUPING(depth3_id) = 0)
        ) a
 ORDER BY a.depth1_id
        , a.depth2_id NULLS FIRST
        , a.depth3_id NULLS FIRST
;