I have a table of employees and managers.
create table hrc (
id int,
employee varchar(255),
manager varchar(255)
);
insert into hrc
values
(1, "A", Null),
(2, "B", "A"),
(3, "C", "B"),
(4, "D", "B"),
(5, "E", "C"),
(6, "E", "D"),
(7, "F", "C"),
(8, "G", "F"),
(9, "H", "E");
So a different thing about this table from all the hierarchy cte examples available out there is "an employee can have more than two managers".
I want to get all the employee in the hierarchy for "B". I have tried this but the below code but it did not work
with hierarchy as
(
select emp, 1 as lvl
from hrc
where emp = 'B'
union all
select h.emp, lvl+1
from hierarchy as h
join hrc as t
on h.emp = t.mang
)
select * from hierarchy
Expected result
B > CD > EF > GH
It looks like you want to group all descendants that are the same distance away from the specified level together. That is, nodes C & D are distance 1 away from node B while nodes E & F are distance 2. Here's what I came up with:
with hierarchy as
(
select emp, mang, 1 as lvl
from #hrc
where emp = 'B'
union all
select child.emp, parent.emp, lvl+1
from hierarchy as parent
join #hrc as child
on child.mang = parent.emp
), lvls as (
select lvl, emp
from hierarchy
group by lvl, emp
)
select lvl, STRING_AGG(emp, '') within group (order by emp)
from lvls
group by lvl;
By way of explanation, I only slightly modified your initial attempt by adding a manager column within the initial CTE to give me a way to join successive levels in the hierarchy. I also renamed the aliases for the base vs recursive elements to parent and child (respectively) as that helps me to keep straight which is which. From there, I did a simple group by lvl to get nodes that are eliminate duplicates. Lastly, I use STRING_AGG to concatenate all nodes at the same level together.