I need some suggestions and best approach to de-normalize a hierarchical structure. For the time being I am using EMP and DEPT tables in SCOTT schema. I might post the actual or near actual example after getting some inputs from this thread.
Now, I want to de-normalize EMP and DEPT to maximum possible level fro my data warehouse.
SELECT e.ename "Employee",
e.deptno,
d.dname,
level,
sys_connect_by_path(e.ename, '/') "Manager Higherarchy"
FROM
emp e
INNER JOIN dept d ON ( e.deptno = d.deptno )
START WITH
ename = 'KING' -- Hardcoding because I know King is the King.. but it will not be in real time
CONNECT BY NOCYCLE PRIOR empno = mgr
AND level <= 10 -- A huger numver
ORDER BY
"Employee",
level,
"Manager Higherarchy";
It gives me something like :

The problem is I can't create a table like EMPNO, ENAME, DEPTNO, DNAME, LEVLE1_MGR_NAME, LEVEL2MRG_NAME ...... etc
Because I don't know where it ends. My question is what is thew best possible approach to de-normalize this.