clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 756653 fiddles created (13614 in the last week).

CREATE TABLE depts ( DEPT_ID, DEPT_NAME, DEPT_PARENT ) AS SELECT 5500, 'World', NULL FROM DUAL UNION ALL SELECT 5510, 'Region 1', 5500 FROM DUAL UNION ALL SELECT 5511, 'Cell 1 Region 1', 5510 FROM DUAL UNION ALL SELECT 5512, 'Cell 2 Region 1', 5510 FROM DUAL UNION ALL SELECT 5513, 'Cell 3 Region 1', 5510 FROM DUAL UNION ALL SELECT 5514, 'Cell 4 Region 1', 5510 FROM DUAL UNION ALL SELECT 5515, 'Cell 5 Region 1', 5510 FROM DUAL UNION ALL SELECT 5520, 'Region 2', 5500 FROM DUAL UNION ALL SELECT 5521, 'Cell 1 Region 2', 5520 FROM DUAL UNION ALL SELECT 5522, 'Cell 2 Region 2', 5520 FROM DUAL UNION ALL SELECT 5530, 'Region 3', 5500 FROM DUAL UNION ALL SELECT 5531, 'Cell 1 Region 3', 5530 FROM DUAL UNION ALL SELECT 5532, 'Cell 2 Region 3', 5530 FROM DUAL UNION ALL SELECT 5540, 'Region 4', 5500 FROM DUAL UNION ALL SELECT 5533, 'Cell 1 Region 4', 5540 FROM DUAL UNION ALL SELECT 5534, 'Cell 2 Region 4', 5533 FROM DUAL UNION ALL SELECT 5590, 'Region 5', 5500 FROM DUAL UNION ALL SELECT 5591, 'Cell 1 Region 5', 5590 FROM DUAL;
18 rows affected
 hidden batch(es)


SELECT CONNECT_BY_ROOT( dept_parent ) AS ancestor, dept_id, SYS_CONNECT_BY_PATH( dept_parent, '>' ) || '>' || dept_id AS path FROM depts START WITH dept_parent IS NOT NULL CONNECT BY PRIOR dept_id = dept_parent;
ANCESTOR DEPT_ID PATH
5500 5510 >5500>5510
5500 5511 >5500>5510>5511
5500 5512 >5500>5510>5512
5500 5513 >5500>5510>5513
5500 5514 >5500>5510>5514
5500 5515 >5500>5510>5515
5500 5520 >5500>5520
5500 5521 >5500>5520>5521
5500 5522 >5500>5520>5522
5500 5530 >5500>5530
5500 5531 >5500>5530>5531
5500 5532 >5500>5530>5532
5500 5540 >5500>5540
5500 5533 >5500>5540>5533
5500 5534 >5500>5540>5533>5534
5500 5590 >5500>5590
5500 5591 >5500>5590>5591
5510 5511 >5510>5511
5510 5512 >5510>5512
5510 5513 >5510>5513
5510 5514 >5510>5514
5510 5515 >5510>5515
5520 5521 >5520>5521
5520 5522 >5520>5522
5530 5531 >5530>5531
5530 5532 >5530>5532
5533 5534 >5533>5534
5540 5533 >5540>5533
5540 5534 >5540>5533>5534
5590 5591 >5590>5591
 hidden batch(es)