--오라클 connect by WITH T(ORG_CD, BF_ORG_CD, REVISION_DT, REAL_NUM) AS ( SELECT 1, NULL, '20210101', 10 FROM DUAL UNION ALL SELECT 5, 1, '20210210', 5 FROM DUAL UNION ALL SELECT 2, NULL, '20210205', 3 FROM DUAL UNION ALL SELECT 3, 5, '20210725', 8 FROM DUAL UNION ALL SELECT 4, 2, '20210326', 1 FROM DUAL ) SELECT ORG_CD, BF_ORG_CD, REVISION_DT, REAL_NUM, CONNECT_BY_ROOT(ORG_CD) GRP FROM T START WITH BF_ORG_CD IS NULL CONNECT BY PRIOR ORG_CD = BF_ORG_CD --재귀 지원 DB, MS SQL 2017에서 테스트 WITH T1(ORG_CD, BF_ORG_CD, REVISION_DT, REAL_NUM) AS ( SELECT 1, NULL, '20210101', 10 UNION ALL SELECT 5, 1, '20210210', 5 UNION ALL SELECT 2, NULL, '20210205', 3 UNION ALL SELECT 3, 5, '20210725', 8 UNION ALL SELECT 4, 2, '20210326', 1 ) , T2(ORG_CD, BF_ORG_CD, REVISION_DT, REAL_NUM, GRP, LV) AS ( SELECT ORG_CD, BF_ORG_CD, REVISION_DT, REAL_NUM, ORG_CD, 1 FROM T1 WHERE BF_ORG_CD IS NULL UNION ALL SELECT A.ORG_CD, A.BF_ORG_CD, A.REVISION_DT, A.REAL_NUM, B.GRP, B.LV+1 FROM T1 A, T2 B WHERE B.ORG_CD = A.BF_ORG_CD ) SELECT ORG_CD, BF_ORG_CD, REVISION_DT, REAL_NUM, GRP FROM T2 ORDER BY GRP, LV