Employee Manager Hierarchy Query in Oracle

Question

I have a table with the structure as below:

+-------+-------+
| EMPID | MGRID |
+-------+-------+
| A     | B     |
| B     | C     |
| C     | D     |
| I     | J     |
| J     | D     |
| K     | L     |
| L     | O     |
| O     | D     |
+-------+-------+

I want to build a query that can create a view as below showing all manager hierarchy as columns starting with highest manager however I am not sure how this can be achieved in oracle. Can someone please help advise how to do this..

Output Desired:

+-------+-------+------+------+------+
| EMPID | MGRID | LVL1 | LVL2 | LVL3 |
+-------+-------+------+------+------+
| A     | B     | D    | C    | B    |
| B     | C     | D    | C    |      |
| C     | D     | D    |      |      |
| I     | J     | D    | J    |      |
| J     | D     | D    |      |      |
| K     | L     | D    | O    | L    |
| L     | O     | D    | O    |      |
| O     | D     | D    |      |      |
+-------+-------+------+------+------+

Note: I am not sure how to add a table to the question but added it as a text so that it can be copied.

It looks like you want self joins here (i.e. join the table to itself): `select ... from emp e left outer join emp mgr1 on mgr1.empid = e.mgrid ...`. — Thorsten Kettner, Aug 26 '20 at 12:31
do not use images for input or output data samples, rather text data please. — Barbaros Özhan, Aug 26 '20 at 12:33

score 1 · Answer 1 · answered Aug 26 '20 at 14:20

You can use Hierarchical Query containing its related functions such as SYS_CONNECT_BY_PATH() and SYS_CONNECT_BY_PATH() including Conditional Aggregation :

WITH emp2 AS
(
 SELECT empid, mgrid, 
        MAX( LENGTH(SYS_CONNECT_BY_PATH(empid, '|'))/2 + 1 ) 
             OVER ( PARTITION BY empid , mgrid ) AS max_lvl, 
        level AS lvl, CONNECT_BY_ROOT(mgrid) AS member
   FROM emp
CONNECT BY PRIOR empid = mgrid
)
SELECT empid, mgrid, 
       MAX( CASE WHEN max_lvl - lvl = 1 THEN member END ) AS LVL1,
       MAX( CASE WHEN max_lvl - lvl = 2 THEN member END ) AS LVL2,
       MAX( CASE WHEN max_lvl - lvl = 3 THEN member END ) AS LVL3
  FROM emp2
 GROUP BY empid, mgrid 
 ORDER BY empid, mgrid;

+-------+-------+------+------+------+
| EMPID | MGRID | LVL1 | LVL2 | LVL3 |
+-------+-------+------+------+------+
| A     | B     | D    | C    | B    |
| B     | C     | D    | C    |      |
| C     | D     | D    |      |      |
| I     | J     | D    | J    |      |
| J     | D     | D    |      |      |
| K     | L     | D    | O    | L    |
| L     | O     | D    | O    |      |
| O     | D     | D    |      |      |
+-------+-------+------+------+------+

Demo

Employee Manager Hierarchy Query in Oracle

1 Answers1