Hi All,
My requirement is to get all the level of children in each row.
For eg
Above is from the standard emp table.
KING is the first level
CLARK BLAKE JONES second level
JAMES WARD TURNER etc third level
My requirement is to display all levels at each row in a seperate column.
The closest query I wrote is below.
SELECT level1,
level2,
level3,
level4,
level5,
level6,
level7,
level8,
empno
FROM ( SELECT LEVEL AS n,
ename AS id,
empno,
ename
FROM (SELECT ename, mgr, empno FROM emp) t
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr)
PIVOT (
MAX (ename)
FOR n
IN (1 AS level1,
2 AS level2,
3 AS level3,
4 AS level4,
5 AS level5,
6 AS level6,
7 AS level7,
8 AS level8)) ;
However it shows the data as below
Thank you
Best Regards
Alex