Hierarchy between employee for each department

Question

I have to build a hierarchy between all the employees working in an organisation department wise.

For e.g. An organisation ABC has 2 departments - Sales, Marketing Each department has multiple employees, some with same name as well. How can I build a hierarchy for each department. Sample data as below

Department	Employee	Manager	Expected_Hierarchy
Sales	John	Kevin	\Kevin
Sales	Adam	John	\John\Adam
Sales	Tom	Adam	\John\Adam\Tom
Sales	Bruce	Tom	\John\Adam\Tom\Bruce
Marketing	Tony	Kevin	\Kevin
Marketing	Bruce	Tony	\Tony\Bruce
Marketing	John	Tony	\Tony\John

enter image description here

I tried creating a hierarchical query but that did not work as the employees with same names kept on getting added in the other department hierarchies.

create table temp_emp (department varchar2(100 char),empname 
varchar2(100 char), manager varchar2(100 char));

insert into temp_emp values ('Sales','John', 'Kevin');
insert into temp_emp values ('Sales','Adam', 'John');
insert into temp_emp values ('Sales','Tom', 'Adam');
insert into temp_emp values ('Sales','Bruce', 'Tom');
insert into temp_emp values ('Marketing','Tony', 'Kevin');
insert into temp_emp values ('Marketing','Bruce', 'Tony');
insert into temp_emp values ('Marketing','John', 'Tony');

Answer 1

You can use:

SELECT t.*,
       SYS_CONNECT_BY_PATH(manager,'/')||'/'||empname AS hierarchy
FROM   temp_emp t
START WITH (department, manager) NOT IN (SELECT department, empname
                                         FROM   temp_emp)
CONNECT BY
       PRIOR department = department
AND    PRIOR empname    = manager;

Which, for the sample data, outputs:

DEPARTMENT	EMPNAME	MANAGER	HIERARCHY
Marketing	Tony	Kevin	/Kevin/Tony
Marketing	Bruce	Tony	/Kevin/Tony/Bruce
Marketing	John	Tony	/Kevin/Tony/John
Sales	John	Kevin	/Kevin/John
Sales	Adam	John	/Kevin/John/Adam
Sales	Tom	Adam	/Kevin/John/Adam/Tom
Sales	Bruce	Tom	/Kevin/John/Adam/Tom/Bruce

fiddle