Employee with their manager levels

Question

Please find below table structure.
I need output in below mentioned format.

E_id	Name	Mg_id
1	Neel	2
2	Abhi	3
3	Neha	null

Output:

Name	Mgr_name1	Mgr_name2
Neel	Abhi	Neha

Answer 1

If you want the employee's name, their manager's name and their manager's manager's name then you can use:

SELECT CONNECT_BY_ROOT name AS name,
       PRIOR name AS mgr_name1,
       name AS mgr_name2
FROM   table_name 
WHERE  LEVEL = 3
START WITH e_id = 1
CONNECT BY PRIOR mg_id = e_id

Which, for the sample data:

CREATE TABLE table_name (E_id, Name, Mg_id) AS
SELECT 1, 'Neel', 2    FROM DUAL UNION ALL
SELECT 2, 'Abhi', 3    FROM DUAL UNION ALL
SELECT 3, 'Neha', NULL FROM DUAL;

Outputs:

NAME	MGR_NAME1	MGR_NAME2
Neel	Abhi	Neha

---

If you wanted to get the output when an employee has no manager and/or no manager's manager then you can use:

SELECT CONNECT_BY_ROOT name AS name,
       CASE LEVEL
       WHEN 2
       THEN name
       WHEN 3
       THEN PRIOR name
       END AS mgr_name1,
       CASE LEVEL
       WHEN 3
       THEN name
       END AS mgr_name2
FROM   table_name 
WHERE  LEVEL = 3
OR     (LEVEL < 3 AND CONNECT_BY_ISLEAF = 1)
START WITH e_id = 1
CONNECT BY PRIOR mg_id = e_id

or you could also use multiple self-joins:

SELECT t1.name,
       t2.name AS mgr_name1,
       t3.name AS mgr_name2
FROM   table_name t1
       LEFT OUTER JOIN table_name t2
       ON t1.mg_id = t2.e_id
       LEFT OUTER JOIN table_name t3
       ON t2.mg_id = t3.e_id
WHERE  t1.e_id = 1;

Note: using self-joins will require reading from the table once for each time the table is joined; this may be less efficient than using a hierarchical query (even though the query is simpler to write).

Which both output the same, but if you run either of those two queries without the START BY filter or WHERE filter respectively then the output is:

NAME	MGR_NAME1	MGR_NAME2
Neel	Abhi	Neha
Abhi	Neha	null
Neha	null	null

fiddle