Recursive SQL self join

Question

I have the following employee table and there is a foreign key relationship between EmpID and ManagerID.

Employee Table

Desired Output

I would like to be able to display the employee names in hierarchical order separated by > from top most manager to the bottom level with the EmpID being the ID of employee at the bottom of the hierarchy.

I know I can get the desired output by joining the table to itself using the following SQL.

select e1.empID, e1.DeptID, e2.Name + ' > ' + e1.Name as Description
from employee e1
left join employee e2
on e1.managerId = e2.empId

I also know that I can add more left joins to the above query to get the desired output. But there is no limit to how deep the hierarchy can be so I guess it will need to be done dynamically.

Any help would be greatly appreciated

Answer 1

declare @Employees table (
    EmpId      int          ,
    DeptId     int          ,
    Name       varchar(30)  ,
    ManagerId  int
);

insert into @Employees values
( 1, 1, 'Zippy'    , 2    ),
( 2, 1, 'George'   , 3    ),
( 3, 1, 'Bungle'   , 4    ),
( 4, 1, 'Geoffrey' , null ),
( 5, 2, 'Rod'      , 6    ),
( 6, 2, 'Jane'     , 7    ),
( 7, 2, 'Freddy'   , null );

with cte as
(
    select   
        EmpId      ,
        DeptId     ,
        ManagerId  ,
        Path       =  cast('' as varchar(4000)),
        Name       ,                
        Level      =  0        
    from
        @Employees    
    where
        ManagerId is null

    union all 

    select 
        EmpId     =  e.EmpId,
        DeptId    =  e.DeptId,
        ParentId  =  e.ManagerId,
        Path      =  cast(concat(cte.Path, cte.Name, ' › ') as varchar(4000)),
        Name      =  e.Name,        
        Level     =  cte.Level + 1    
    from 
        @Employees e
        inner join cte on cte.EmpId = e.ManagerId
)
select 
    EmpId      ,
    DeptId     ,
    ManagerId  ,    
    Path       ,
    Name       ,
    FullPath   =  Path + Name,    
    Level        
from 
    cte
order by
    FullPath;

Answer 2

You want a recursive CTE:

with e as (
      select cast(name as varchar(max)) as list, empId, 0 as level
      from employees
      where managerID is null
      union all
      select e.list + '>' + e2.name, e2.empId, level + 1
      from e join
           employees e2
           on e.empId = e2.managerId
     )
select e.*
from e
where not exists (select 1
                  from employees e2
                  where e2.managerId = e.empId
                 );