CODEWITHSUNDEEP
oracle-databaserecursionhierarchical-data
suk15
suk15

Reputation: 49

Recursive hierarchical Oracle SQL query

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.

Upvotes: 0

Views: 661

Answers (3)

access_granted
access_granted

Reputation: 1907

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;

Upvotes: 0

Debabrata
Debabrata

Reputation: 162

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;

Upvotes: 1

Jair Hernandez
Jair Hernandez

Reputation: 494

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;

Upvotes: 0

Related Questions