CODEWITHSUNDEEP
sqloracle-database
shockwave
shockwave

Reputation: 3272

SQL Employee table with concatenated direct reports hierarchy

I have an employee table with the structure shown below. I'm trying to build a column with comma separated values which are direct reports to the employee. I've used manager names to easily understand. The actual data has IDs.

EMPID     NAME     MANAGER_1     MANAGER_2     MANAGER_3     MANAGER_4.......     MANAGER_30
A3456     JOHN     MEGAN         DEREK         MIKE          NULL
S4593     ASHTON   MEGAN         DEREK         MIKE          NULL
G8394     MICHAEL  MEGAN         DEREK         MIKE          NULL
D9038     MIKE     MEGAN         DEREK         NULL
J4890     DEREK    MEGAN         NULL
M4940     MEGAN    NULL

The hierarchy is as follows JOHN's immediate manager is Mike, Mike reports to Derek, Derek reports to Megan ASHTON's immediate manager is Mike, Mike reports to Derek, Derek reports to Megan.

I'm trying to build a new column which will create a comma separted list of direct reports eg: for MIKE direct reports will be JOHN, ASHTON, MICHAEL for DEREK direct reports will be MIKE

Expected result

EMPID     NAME     MANAGER_1     MANAGER_2     MANAGER_3     MANAGER_4.......     Direct Reports     
A3456     JOHN     MEGAN         DEREK         MIKE          NULL                 NULL          
S4593     ASHTON   MEGAN         DEREK         MIKE          NULL                 NULL          
G8394     MICHAEL  MEGAN         DEREK         MIKE          NULL                 NULL          
D9038     MIKE     MEGAN         DEREK         NULL                               JOHN, ASHTON, MICHAEL
J4890     DEREK    MEGAN         NULL                                             MIKE
M4940     MEGAN    NULL                                                           MEGAN

To build this in SQL should I use partition logic?

Upvotes: 0

Views: 175

Answers (1)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

That's pretty easy - just use coalesce:

This query will give you what you need:

select 
   coalesce(MANAGER_30, MANAGER_29,...MANAGER_1) NAME
  ,listagg(name,',')within group(order by name) "Direct Reports"
grom your_table
group by 
   coalesce(MANAGER_30, MANAGER_29,...MANAGER_1)

You just will need to left join results to your original query.

PS. But such structure is very inconvenient. I'd suggest normal hierarchy table with parent->child relationship

Upvotes: 1

Related Questions