CODEWITHSUNDEEP
sqloracle-databaserecursive-query
Raghava Ch
Raghava Ch

Reputation: 77

Display parent child hierarchy in oracle

I have an Oracle table like below


EMPNO   EMPNAME     MANAGERID
1       EMP1    
2       EMP2          1
3       EMP3          2

By using above table, I want to display employee manager hierarchy like below,


EMPNO   EMPNAME     EMPHIERARCHY    (Contains EMP name and Manager Name(Managers Manager as well))
1       EMP1    
2       EMP2        EMP2,EMP1
3       EMP3        EMP3,EMP2,EMP1

Could anyone please guide me how to achieve this in Oracle?.

Upvotes: 0

Views: 4057

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

For "standard" hierarchy you need this query:

select empno, empname, sys_connect_by_path(empname, '/') hierarchy
  from test connect by managerid = prior empno start with managerid is null

Output:

      EMPNO EMPNAME    HIERARCHY
----------- ---------- ----------------
          1 EMP1       /EMP1
          2 EMP2       /EMP1/EMP2
          3 EMP3       /EMP1/EMP2/EMP3

For hierarchy "inverted" this query worked:

SQLFiddle

select root1 empno, root2 empname, hierarchy from (
  select empno, empname, managerid, prior empname,
      sys_connect_by_path(empname, '/') hierarchy, connect_by_isleaf leaf,
      connect_by_root(empno) root1, connect_by_root(empname) root2
    from test connect by prior managerid = empno
  ) where leaf = 1

Output:

     EMPNO EMPNAME    HIERARCHY
---------- ---------- ----------------
         1 EMP1       /EMP1
         2 EMP2       /EMP2/EMP1
         3 EMP3       /EMP3/EMP2/EMP1

More about hierarchical queries.

Upvotes: 2

Related Questions