SQL Reports for Manager/Supervisor/Employee Hierarchy from an Employee Table

Question

I have an Employee table which show an employee ID and their manager ID. The managers want reports that will not only show his direct reports but also himself and all those employees who report to his direct reports.

For example, my table would have the following table:

The report I need to write for Manager1 would have his information as well as his direct reports (Supervisor1 and Supervisor2) and their reports (Employee1 through Employee4).

Is there some kind of Join I can use or any other code to relate this one table back to itself multiple time to pick up the indirect reports?

Answer 1

A recursive Common Table Expression is what you are looking for:

declare @Employees table (EmpID nvarchar(15), MgrID nvarchar(15));

insert into @Employees values ('Manager1', null);
insert into @Employees values ('Supervisor1', 'Manager1');
insert into @Employees values ('Employee1', 'Supervisor1');
insert into @Employees values ('Employee2', 'Supervisor1');
insert into @Employees values ('Supervisor2', 'Manager1');
insert into @Employees values ('Employee3', 'Supervisor2');
insert into @Employees values ('Employee4', 'Supervisor2');

with Employees (MgrID, EmpID, [Rank], [Reports]) as
(
    select
      MgrID
    , EmpID
    , 0 'Rank'
    , cast(EmpID as nvarchar(max)) 'Reports'
    from @Employees
    where MgrID is null
    union all
    select
      e.MgrID 
    , e.EmpID
    , [Rank] + 1
    , [Reports] + '; ' + e.EmpID
    from @Employees e
    inner join Employees on Employees.EmpID = e.MgrID
)
select *
from Employees
where Reports like 'Manager1%'
order by [Rank];