Recursive hierarchical Oracle SQL query

Question

I have a source table like below:

Emp_ID| Name| Manager_ID
001|abc|005
005|cde|010
010|xyz|050
050 | bcg| 100
100|sta|NULL

My requirement is to populate the target table like below:

Emp_ID| Name| Manager_1| Manager_2| Manager_3| Manager_4
005|cde|xyz|bcg|sta|NULL
050|bcg|sta| NULL|NULL|NULL
100|sta|NULL|NULL|NULL
001|abc|cde|xyz|bcg|sta

I am able to use recursive select through Connect by clause and populate the value for Manager_1 but not able to get through the logic to populate Manager_2, Manager_3 , Manager_4 values as different column values in a single row depending on how many level of hierarchy is present for a certain employee. Please help.

The requirement is nonsensical. Unless you know "through external means" (without looking at the data), you won't even know how far (how many columns) the output needs to extend, and without knowing the number of columns ahead of time, it's impossible to write a SQL query. You would need dynamic SQL, which is not recommended and is often a sign of poor design. (The output to this query is clearly poorly designed.) Is this a work assignment or something they teach in class? — user5683823
– user5683823, Commented Jun 6, 2017 at 18:22
What is your current query, and what output does it produce? — Alex Poole
– Alex Poole, Commented Jun 6, 2017 at 21:40
@mathguy: Its kind of work assignment.. which I have tried using 'with select' clause and path options available in Oracle SQL — suk15
– suk15, Commented Jun 7, 2017 at 4:41
@access_granted: can you please elaborate a little more on 'Pivot' option. It will be helpful. — suk15
– suk15, Commented Jun 7, 2017 at 4:42

Debabrata · Accepted Answer · 2017-06-07 07:29:51Z

1

I think the following query will help you. But to split the string to individual manager id, you need to know the max no of level of managers.

WITH data_set AS
     (SELECT '001' emp_id, 'aaa' emp_name, '005' mgr_id
        FROM DUAL
      UNION
      SELECT '005' emp_id, 'bbb' emp_name, '010' mgr_id
        FROM DUAL
      UNION
      SELECT '010' emp_id, 'ccc' emp_name, '050' mgr_id
        FROM DUAL
      UNION
      SELECT '020' emp_id, 'ddd' emp_name, '050' mgr_id
        FROM DUAL
      UNION
      SELECT '050' emp_id, 'eee' emp_name, '100' mgr_id
        FROM DUAL
      UNION
      SELECT '100' emp_id, 'fff' emp_name, '200' mgr_id
        FROM DUAL
      UNION
      SELECT '200' emp_id, 'ggg' emp_name, NULL mgr_id
        FROM DUAL)
SELECT     emp_id, emp_name, mgr_id,
           LTRIM (SYS_CONNECT_BY_PATH (emp_id, '-'), '-') chain
      FROM data_set
START WITH mgr_id IS NULL
CONNECT BY mgr_id = PRIOR emp_id
  ORDER SIBLINGS BY emp_id;

answered Jun 7, 2017 at 7:29

Debabrata

1629 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Debabrata Over a year ago

Can u post the final query as I am curious about how you finally handled it. Also please mark the answer acceptable if it solved your query @suk15

Jair Hernandez · Accepted Answer · 2017-06-06 18:29:01Z

0

If your hierarchy only extends to 4 levels deep, the following query may be used:

select t1.Emp_ID, 
       t1.Name, 
       t2.Name as Manager_1, 
       t3.Name as Manager_2, 
       t4.Name as Manager_3,
       t5.Name as Manager_4
from tmp t1
left join tmp t2 on t2.Emp_ID = t1.Manager_ID
left join tmp t3 on t3.Emp_ID = t2.Manager_ID
left join tmp t4 on t4.Emp_ID = t3.Manager_ID
left join tmp t5 on t5.Emp_ID = t4.Manager_ID;

answered Jun 6, 2017 at 18:29

Jair Hernandez

4943 silver badges7 bronze badges

1 Comment

suk15 Over a year ago

It will not be always 4 level deep.. it is dynamic.. it can be 10 also or more than that..

access_granted · Accepted Answer · 2017-06-07 19:08:19Z

0

Pivot option:

SELECT * FROM
(
  SELECT emp_id, name, manager_id
  FROM employees
)
PIVOT
(
  COUNT(manager_id)
  FOR manager_id IN ('005', '100', '050')
)
ORDER BY emp_id;

edited Jun 7, 2017 at 19:08

answered Jun 7, 2017 at 19:01

access_granted

2,03123 silver badges29 bronze badges

1 Comment

suk15 Over a year ago

Thank you all for help. @Debabrata answer helped me to think of the final solution

Collectives™ on Stack Overflow

Recursive hierarchical Oracle SQL query

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related