-- Oracle --
WITH t AS
(
SELECT 9 seq, 7 pk_id, 2 depth, '21A063' foldername FROM dual
UNION ALL SELECT 10,  8, 7, 'UV LASER CUT SYSTEM' FROM dual
UNION ALL SELECT 11,  9, 8, '00_SOFTWARE_UNIT'    FROM dual
UNION ALL SELECT 12, 10, 9, '*01_DIGITAL_IO'      FROM dual
UNION ALL SELECT 13, 11, 9, '*02_INDUSTRIAL_PC'   FROM dual
UNION ALL SELECT 15, 13, 8, '20_WORK TABLE UNIT'  FROM dual
UNION ALL SELECT 17, 14, 9, '*03_VISION'          FROM dual
)
SELECT seq
     , pk_id
     , NVL(PRIOR pk_id, depth) depth
     , foldername
  FROM (SELECT (SELECT MAX(seq  ) FROM t) + ROWNUM seq
             , (SELECT MAX(pk_id) FROM t) + ROWNUM pk_id
             , pk_id old_pk_id
             , depth
             , foldername
          FROM t
         START WITH pk_id = 7
         CONNECT BY PRIOR pk_id = depth
        )
 START WITH old_pk_id = 7
 CONNECT BY PRIOR old_pk_id = depth
;

-- database 버전은 미리 알려주셔야할꺼 같아요. 

WITH T (seq, pk_id, depth, foldername) AS (
    SELECT 9 ,  7, 2, '21A063'                UNION ALL
    SELECT 10,  8, 7, '  UV LASER CUT SYSTEM' UNION ALL
    SELECT 11,  9, 8, '  00_SOFTWARE_UNIT'    UNION ALL
    SELECT 12, 10, 9, '    *01_DIGITAL_IO'    UNION ALL
    SELECT 13, 11, 9, '    *02_INDUSTRIAL_PC' UNION ALL
    SELECT 15, 13, 8, '  20_WORK TABLE UNIT'  UNION ALL
    SELECT 17, 14, 9, '    *03_VISION'         
), TT AS (
    SELECT
        MAX(seq) OVER() + ROW_NUMBER() OVER (ORDER BY seq) seq
        , MAX(pk_id) OVER() + ROW_NUMBER() OVER (ORDER BY seq) pk_id
        , CASE WHEN foldername = '21A063' THEN depth ELSE COUNT(seq) OVER() + 1 + depth END  depth
        , foldername
    FROM T
) 
SELECT 
    seq, pk_id, depth, foldername  
FROM TT WHERE depth = 2 
UNION ALL
SELECT
    MAX(T1.seq) seq, MAX(T1.pk_id) pk_id, MAX(T3.pk_id) pk_id, T1.foldername
FROM TT T1, TT T2, TT T3
WHERE T1.depth = T2.pk_id AND T2.depth = T3.depth AND T1.pk_id > T3.pk_id
GROUP BY T1.foldername
ORDER BY seq

-- MSSQL --
WITH t AS
(
SELECT 9 seq, 7 pk_id, 2 depth, '21A063' foldername
UNION ALL SELECT 10,  8, 7, 'UV LASER CUT SYSTEM'
UNION ALL SELECT 11,  9, 8, '00_SOFTWARE_UNIT'
UNION ALL SELECT 12, 10, 9, '*01_DIGITAL_IO'
UNION ALL SELECT 13, 11, 9, '*02_INDUSTRIAL_PC'
UNION ALL SELECT 15, 13, 8, '20_WORK TABLE UNIT'
UNION ALL SELECT 17, 14, 9, '*03_VISION'
)
, t1 AS
( -- 1. 특정 폴더 및 하위 폴더 가져오기 --
SELECT *
  FROM t
 WHERE pk_id = 7
 UNION ALL
SELECT a.*
  FROM t a
     , t1 b
 WHERE b.pk_id = a.depth
)
, t2 AS
( -- 2. 새로운 번호(seq, pk_id) 부여하기 --
SELECT (SELECT MAX(seq  ) FROM t) + ROW_NUMBER() OVER(ORDER BY seq) seq
     , (SELECT MAX(pk_id) FROM t) + ROW_NUMBER() OVER(ORDER BY seq) pk_id
     , pk_id old_pk_id
     , depth
     , foldername
  FROM t1
)
-- 3. depth 가져오기 --
SELECT a.seq
     , a.pk_id
     , ISNULL(b.pk_id, a.depth) depth
     , a.foldername
  FROM t2 a
  LEFT OUTER JOIN t2 b
    ON a.depth = b.old_pk_id
;