By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name ( CaseId1, CaseID2 ) AS
SELECT 'A', 'B' FROM DUAL UNION ALL
SELECT 'B', 'C' FROM DUAL UNION ALL
SELECT 'C', 'D' FROM DUAL UNION ALL
SELECT 'E', 'F' FROM DUAL UNION ALL
SELECT 'F', 'G' FROM DUAL UNION ALL
SELECT 'H', 'I' FROM DUAL UNION ALL
SELECT 'I', 'X' FROM DUAL UNION ALL
SELECT 'Z', 'X' FROM DUAL
8 rows affected
SELECT t.*,
DENSE_RANK() OVER ( ORDER BY CONNECT_BY_ROOT( CaseId1 ) ) AS group_id
FROM table_name t
START WITH NOT EXISTS (
SELECT 1
FROM table_name x
WHERE x.ROWID != t.ROWID
AND x.caseId2 IN ( t.CaseId1, t.CaseId2 )
)
CONNECT BY NOCYCLE
PRIOR ROWID != ROWID
AND PRIOR CaseId2 IN ( CaseId1, CaseId2 )
| CASEID1 | CASEID2 | GROUP_ID |
|---|---|---|
| A | B | 1 |
| B | C | 1 |
| C | D | 1 |
| E | F | 2 |
| F | G | 2 |
| H | I | 3 |
| I | X | 3 |
| Z | X | 3 |