Skip to Main Content

SQL & PL/SQL

SYS_CONNECT_BY_PATH results to individual rows.

RengudiDec 14 2022

Hi Gurus,
All is well.
I am Working in oracle version 19c.

Question:-

 
Input:-
 
SELECT LVL,
       SYS_CONNECT_BY_PATH(name, '->') AS Path
FROM (SELECT 'A' AS name, 0 AS "LVL"
      FROM dual
      UNION
      SELECT 'B', 1
      FROM dual
      UNION
      SELECT 'C', 2
      FROM dual
      UNION
      SELECT 'D', 3
      FROM dual)
START WITH LVL = 0
CONNECT BY PRIOR LVL+1 = LVL
ORDER SIBLINGS BY LVL;


+---+--------------+
|LVL| Path         |
+===+==============+
| 0 | ->A          |
+---+--------------+
| 1 | ->A->B       |
+---+--------------+
| 2 | ->A->B->C    |
+---+--------------+
| 3 | ->A->B->C->D |
+---+--------------+


The query returns the level and the path (Using sys_connect_by_path and '->' as the separator).


Is it possible to make each item in the path in its own row? 


So that, expected output would be look like below


+--------+----------+
| LVL_OP | SPLIT_OP |
+========+==========+
| 0      | A        |
+--------+----------+
| 1      | A        |
+--------+----------+
| 1      | B        |
+--------+----------+
| 2      | A        |
+--------+----------+
| 2      | B        |
+--------+----------+
| 2      | C        |
+--------+----------+
| 3      | A        |
+--------+----------+
| 3      | B        |
+--------+----------+
| 3      | C        |
+--------+----------+
| 3      | D        |
+--------+----------+


Thanks


This post has been answered by Frank Kulash on Dec 14 2022
Jump to Answer
Comments
Post Details
Added on Dec 14 2022
7 comments
216 views