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