Oracle - using connect by prior in Hierarchical Queries and put them into rows

Question

I am new in Oracle, I am going to use connect by prior to implement the flat table instead of Hierarchical one. but I am a little bit confuse. My table is like this: empTabl:

empID	empName	managerID
100	Sara	110
101	Ben	111
102	Alex	110
110	Ross	111
111	Mon	NULL

I am going to change the table like this(output):

emp	empName	subBoss	subBossName	Boss	BossName
100	Sara	110	Ross	111	Mon
101	Ben	111	Mon	NULL	NULL
102	Alex	110	Ross	111	Mon
110	Ross	111	Mon	NULL	NULL
111	Mon	NULL	NULL	NULL	NULL

score 1 · Answer 1 · answered Sep 08 '21 at 09:30

You can also use connect by clause for that purpose.

select empID, empName
  , subBoss, subBossName
  , (select boss.empID from YourTable boss where boss.empID = subBossManagerID) Boss
  , (select boss.empName from YourTable boss where boss.empID = subBossManagerID) BossName
from (
  select empID, empName
    , prior empID subBoss, prior empName subBossName
    , prior t.managerID subBossManagerID
  from YourTable t
  start with managerID is null
  connect by prior empID = managerID
)
order by 1
;

test here

Glad to see I could help. – Mahamoutou Sep 08 '21 at 15:02 — Mahamoutou, Sep 08 '21 at 15:02

score 1 · Answer 2 · answered Sep 08 '22 at 09:52

Ankit Bajpai's answer gives wrong name for manager after level 4 because of connect by root usage. With small notification, this query gives shortest answer

WITH DATA AS (SELECT 100 AS empID, 'Sara' AS empName, 110 AS managerID FROM DUAL UNION ALL
              SELECT 101, 'Ben', 111 FROM DUAL UNION ALL
              SELECT 102, 'Alex', 110 FROM DUAL UNION ALL
              SELECT 110, 'Ross', 111 FROM DUAL UNION ALL
              SELECT 111, 'Mon', NULL FROM DUAL)
, b as( SELECT p.empID , p.empName, p.managerID, m.empName as managerName  FROM DATA p left join data m
   on ( p.managerID=m.empID))
 SELECT empID emp, empName, prior empID subBoss, prior empName subBossName
    , prior managerID Boss, prior managerName BossNAme
  FROM b
 START WITH managerID IS NULL
CONNECT BY PRIOR empID = managerID
  ORDER BY 1;

fiddle

MT0 · Answer 3 · 2021-09-08T09:26:04.887

You can use a recursive sub-query factoring clause for this:

WITH hierarchy (empID, empName, subBoss, subBossName, boss, bossName, depth, managerId) AS (
  SELECT empID,
         empName,
         CAST(NULL AS NUMBER),
         CAST(NULL AS VARCHAR2(20)),
         CAST(NULL AS NUMBER),
         CAST(NULL AS VARCHAR2(20)),
         1,
         managerID
  FROM   empTbl
UNION ALL
  SELECT h.empID,
         h.empName,
         CASE h.depth
         WHEN 1 THEN e.empID
         ELSE h.subBoss
         END,
         CASE h.depth
         WHEN 1 THEN e.empName
         ELSE h.subBossName
         END,
         CASE h.depth
         WHEN 2 THEN e.empID
         ELSE h.boss
         END,
         CASE h.depth
         WHEN 2 THEN e.empName
         ELSE h.bossName
         END,
         h.depth + 1,
         e.managerID
  FROM   hierarchy h
         LEFT OUTER JOIN empTbl e
         ON (h.managerID = e.empID)
  WHERE  depth < 3
)
CYCLE empID, depth SET is_cycle TO 1 DEFAULT 0
SELECT empID, empName, subBoss, subBossName, boss, bossName
FROM   hierarchy
WHERE  depth = 3;

Or, you can use a hierarchical query and pivot:

SELECT emp_id AS empID,
       emp_name AS empName,
       subboss_id AS subbossid,
       subboss_name AS subbossname,
       boss_id AS bossid,
       boss_name AS bossname
FROM   (
  SELECT CONNECT_BY_ROOT(empID) AS root_empid,
         empID,
         empName,
         LEVEL AS depth
  FROM   empTbl
  WHERE  LEVEL <= 3
  CONNECT BY PRIOR managerID = empID
)
PIVOT (
  MAX(empID) AS id,
  MAX(empName) AS name
  FOR depth IN (
    1 AS emp,
    2 AS subBoss,
    3 AS boss
  )
)
ORDER BY empid;

Which, for the sample data:

CREATE TABLE empTbl (empID, empName, managerID) AS
SELECT 100, 'Sara', 110 FROM DUAL UNION ALL
SELECT 101, 'Ben',  111 FROM DUAL UNION ALL
SELECT 102, 'Alex', 110 FROM DUAL UNION ALL
SELECT 110, 'Ross', 111 FROM DUAL UNION ALL
SELECT 111, 'Mon',  NULL FROM DUAL;

Both output:

EMPID EMPNAME SUBBOSS SUBBOSSNAME BOSS BOSSNAME

100 Sara 110 Ross 111 Mon

102 Alex 110 Ross 111 Mon

101 Ben 111 Mon

110 Ross 111 Mon

111 Mon

db<>fiddle here

score 0 · Answer 4 · answered Sep 08 '21 at 09:43

The shorter version of MDO's answer would be -

WITH DATA AS (SELECT 100 AS empID, 'Sara' AS empName, 110 AS managerID FROM DUAL UNION ALL
              SELECT 101, 'Ben', 111 FROM DUAL UNION ALL
              SELECT 102, 'Alex', 110 FROM DUAL UNION ALL
              SELECT 110, 'Ross', 111 FROM DUAL UNION ALL
              SELECT 111, 'Mon', NULL FROM DUAL)
 SELECT empID emp, empName, prior empID subBoss, prior empName subBossName
    , prior t.managerID Boss
    ,CASE WHEN PRIOR t.managerID IS NOT NULL THEN CONNECT_BY_ROOT(t.empName) END AS BossName
  FROM DATA T
 START WITH managerID IS NULL
CONNECT BY PRIOR empID = managerID
  ORDER BY 1;

Demo.

If the hierarchy has 4 (or more) levels then this would get the `boss` values for `id` and `name` from different levels of the hierarchy [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=fa99105aaf4d393f500ffb2db78edf50). — MT0, Sep 08 '21 at 10:46

Oracle - using connect by prior in Hierarchical Queries and put them into rows

4 Answers4