SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to find the manager hierarchy

Aman KhanDec 15 2023

Hi,

I am writing an sql to get manager hierarchy all in one row.

Sample Data:

EMPID EMPNAME MGRID MGRNAME

20125 AX123 20130 ZX987

20130 ZX987 20198 BY234

20198 BY234 20156 YU875

20156 YU875 20001 CEO

20001 CEO NA NA

I am looking for, if I take an employee, I want to look at Employee Mgr Hirerarchy in same row, like:

If I take 20125 EMPID,

EMPID EMPNAME EC1 EC1NAME EC0 EC0NAME

20198 BY234 20156 YU875 20001 CEO

If I take 20125 EMPID,

EMPID EMPNAME EC3 EC3NAME EC2 EC2NAME EC1 EC1NAME EC0 EC0NAME

20125 AX123 20130 ZX987 20198 BY234 20156 YU875 20001 CEO

Please advise how I can acheive the above scenario.

Thanks

AMan

Added on Dec 15 2023

12 comments

2,391 views

1 mention