Hello AskTOM Team,
with the schema as provided in the LiveSQL Link (which is simply the example EMP/DEPT schema), I ran the following query
select case
when LEVEL = 1 then
ENAME
else
rpad('| ', (LEVEL - 2) * 3, '| ') || '|--' || ENAME
end ENAME
from EMP
connect by prior EMPNO = MGR
start with MGR is null
order siblings by EMPNOto get this output
KING
|--JONES
| |--SCOTT
| | |--ADAMS
| |--FORD
| | |--SMITH
|--BLAKE
| |--ALLEN
| |--WARD
| |--MARTIN
| |--TURNER
| |--JAMES
|--CLARK
| |--MILLER
Now, what I'd like, is the parent nodes to repeat each time there's a new leaf. So it basically should look like this
KING
|--JONES
| |--SCOTT
| | |--ADAMS
KING
|--JONES
| |--FORD
| | |--SMITH
KING
|--BLAKE
| |--ALLEN
KING
|--BLAKE
| |--WARD
KING
|--BLAKE
| |--MARTIN
KING
|--BLAKE
| |--TURNER
KING
|--BLAKE
| |--JAMES
KING
|--CLARK
| |--MILLER
Is there a way to achieve this? I played around with connect_by_root and connect_by_isleaf, but wasn't able to mirror my desired result.
Thanks in advance for taking your time answering this question!
Here's one way to do it:
- Use sys_connect_by_path to build path from the root to each row as a CSV
- Identify the leaves using connect_by_isleaf
- Then for each of the leaves, generate a row for each node in the hierarchy using your favourite "CSV to rows" method
For example:
with rws as (
select case
when LEVEL = 1 then
ENAME
else
rpad('| ', (LEVEL - 2) * 3, '| ') || '|--' || ENAME
end ENAME,
sys_connect_by_path(ename, ',') pth,
connect_by_isleaf lf
from scott.EMP
connect by prior EMPNO = MGR
start with MGR is null
order siblings by EMPNO
), leaves as (
select * from rws
where lf = 1
)
select pth, rpad('| ', (l ) * 3, '| ') || regexp_substr(pth, '[^,]+', 1, l) nm
from leaves, lateral (
select level l
from dual
connect by level <= length(regexp_replace(pth, '[^,]'))
)
order by pth, l;
PTH NM
,KING,BLAKE,ALLEN | KING
,KING,BLAKE,ALLEN | | BLAKE
,KING,BLAKE,ALLEN | | | ALLEN
,KING,BLAKE,JAMES | KING
,KING,BLAKE,JAMES | | BLAKE
,KING,BLAKE,JAMES | | | JAMES
,KING,BLAKE,MARTIN | KING
,KING,BLAKE,MARTIN | | BLAKE
,KING,BLAKE,MARTIN | | | MARTIN
,KING,BLAKE,TURNER | KING
,KING,BLAKE,TURNER | | BLAKE
,KING,BLAKE,TURNER | | | TURNER
,KING,BLAKE,WARD | KING
,KING,BLAKE,WARD | | BLAKE
,KING,BLAKE,WARD | | | WARD
,KING,CLARK,MILLER | KING
,KING,CLARK,MILLER | | CLARK
,KING,CLARK,MILLER | | | MILLER
,KING,JONES,FORD,SMITH | KING
,KING,JONES,FORD,SMITH | | JONES
,KING,JONES,FORD,SMITH | | | FORD
,KING,JONES,FORD,SMITH | | | | SMITH
,KING,JONES,SCOTT,ADAMS | KING
,KING,JONES,SCOTT,ADAMS | | JONES
,KING,JONES,SCOTT,ADAMS | | | SCOTT
,KING,JONES,SCOTT,ADAMS | | | | ADAMS