Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test 
(
emp_id number ,
emp_name varchar2(10),
mgr_id number );
insert into test values(1,'amar',null);
1 rows affected

insert into test values(2,'ram',1);
1 rows affected

insert into test values(3,'raju',2);
1 rows affected

insert into test values(4,'vinod',3);
1 rows affected

insert into test values(5,'amar1',null);
1 rows affected

insert into test values(6,'ram1',5);
1 rows affected

insert into test values(7,'raju1',6);
1 rows affected

insert into test values(8,'vinod1',7);
1 rows affected

with rcte (root_id, emp_id) as (
  select emp_id, emp_id
  from test
  where mgr_id is null
  union all
  select root_id, t.emp_id
  from rcte r
  join test t on t.mgr_id = r.emp_id
)
select * from rcte


ROOT_ID
EMP_ID


1
1

5
5

1
2

5
6

1
3

5
7

1
4

5
8


with rcte (root_id, emp_id) as (
  select emp_id, emp_id
  from test
  where mgr_id is null
  union all
  select root_id, t.emp_id
  from rcte r
  join test t on t.mgr_id = r.emp_id
)
select root_id as emp_id,
  listagg(emp_id, ',') within group (order by emp_id) as emp_id_under_manager
from rcte
where emp_id != root_id
group by root_id


EMP_ID
EMP_ID_UNDER_MANAGER


1
2,3,4

5
6,7,8


select connect_by_root(emp_id) as root_id, emp_id, emp_name
from test
connect by mgr_id = prior emp_id
start with mgr_id is null


ROOT_ID
EMP_ID
EMP_NAME


1
1
amar

1
2
ram

1
3
raju

1
4
vinod

5
5
amar1

5
6
ram1

5
7
raju1

5
8
vinod1


select root_id as emp_id,
  listagg(emp_id, ',') within group (order by emp_id) as emp_id_under_manager
from (
  select connect_by_root(emp_id) as root_id, emp_id
  from test
  connect by mgr_id = prior emp_id
  start with mgr_id is null
)
where emp_id != root_id
group by root_id


EMP_ID
EMP_ID_UNDER_MANAGER


1
2,3,4

5
6,7,8


WITH RECURSIVE employee_paths (emp_id, emp_name, path) AS
(
  SELECT e.emp_id, e.emp_name, CAST(e.emp_id AS CHAR(200))  
    FROM test e 
    WHERE mgr_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.emp_name, CONCAT(ep.path, ',', e.emp_id)
    FROM employee_paths ep JOIN test  e
      ON ep.emp_id = e.mgr_id
)
SELECT * FROM employee_paths ORDER BY path;
ORA-00905: missing keyword

WITH employee_paths (emp_id, emp_name, path) AS
(
  SELECT e.emp_id, e.emp_name, CAST(e.emp_id AS CHAR(200))
    FROM test e 
    WHERE mgr_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.emp_name, ep.path || ',' || e.emp_id
    FROM employee_paths ep JOIN test  e
      ON ep.emp_id = e.mgr_id
)
SELECT * FROM employee_paths ORDER BY path;


EMP_ID
EMP_NAME
PATH


1
amar
1

2
ram
1                                                                                                                                                                                                       ,2

3
raju
1                                                                                                                                                                                                       ,2,3

4
vinod
1                                                                                                                                                                                                       ,2,3,4

5
amar1
5

6
ram1
5                                                                                                                                                                                                       ,6

7
raju1
5                                                                                                                                                                                                       ,6,7

8
vinod1
5                                                                                                                                                                                                       ,6,7,8


WITH employee_paths (emp_id, emp_name, path) AS
(
  SELECT e.emp_id, e.emp_name, to_char(e.emp_id)
    FROM test e 
    WHERE mgr_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.emp_name, ep.path || ',' || e.emp_id
    FROM employee_paths ep JOIN test  e
      ON ep.emp_id = e.mgr_id
)
SELECT * FROM employee_paths ORDER BY path;


EMP_ID
EMP_NAME
PATH


1
amar
1

2
ram
1,2

3
raju
1,2,3

4
vinod
1,2,3,4

5
amar1
5

6
ram1
5,6

7
raju1
5,6,7

8
vinod1
5,6,7,8
EMP_ID	EMP_NAME	PATH
1	amar	1
2	ram	1 ,2
3	raju	1 ,2,3
4	vinod	1 ,2,3,4
5	amar1	5
6	ram1	5 ,6
7	raju1	5 ,6,7
8	vinod1	5 ,6,7,8
EMP_ID	EMP_NAME	PATH
1	amar	1
2	ram	1,2
3	raju	1,2,3
4	vinod	1,2,3,4
5	amar1	5
6	ram1	5,6
7	raju1	5,6,7
8	vinod1	5,6,7,8