Oracle Hierarchical Query Example - Multiple Columns on connect by clause

Oracle Hierarchical Query Example  - Multiple Columns on connect by clause

Dear Readers,

This post is really a quick one where you can see how can we write a Hierarchical Query CONNECT BY Clause with multiple columns.

Good Reads:

Thanks to Tim Hall for his tutorial here a quick start one and then oracle guide here.

Setup:

CREATE TABLE tab1
(
  id        NUMBER,
  name      varchar2(10),
  parent_id NUMBER,
  pname      varchar2(10)
);

INSERT INTO tab1 VALUES (1, 'ram',NULL,NULL);
INSERT INTO tab1 VALUES (2, 'rose',1,'ram');
INSERT INTO tab1 VALUES (3, 'kim',2,'rose');
INSERT INTO tab1 VALUES (4, 'kuber',2,'rose');
COMMIT;

Query:

SELECT id||','||name id_nm,
       parent_id||','||pname pid_nm,
       RPAD('.', (level-1)*2, '.') || id||','||name AS tree,
       level,
       CONNECT_BY_ROOT (id||','||name) AS root_id,
      LTRIM(SYS_CONNECT_BY_PATH((id||','||name), '->'),'->') AS path,
       CONNECT_BY_ISLEAF AS leaf
FROM   tab1
START WITH (parent_id||pname) IS NULL
CONNECT BY prior (id||name) = (parent_id||pname)
ORDER SIBLINGS BY (id||name)
;

Result:

ID_NM      PID_NM     TREE                      LEVEL ROOT_ID    PATH                           LEAF
---------- ---------- -------------------- ---------- ---------- ------------------------ ----------
1,ram      ,          1,ram                         1 1,ram      1,ram                             0
2,rose     1,ram      ..2,rose                      2 1,ram      1,ram->2,rose                     0
3,kim      2,rose     ....3,kim                     3 1,ram      1,ram->2,rose->3,kim              1
4,kuber    2,rose     ....4,kuber                   3 1,ram      1,ram->2,rose->4,kuber            1
 
 Thanks for your visit!