A 递归公用表表达式就是你要找的:

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];