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

Use FOR XML PATH as shown in: http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — Glenn
– Glenn, Commented Jan 13, 2017 at 11:32
Maybe you should consider using plsql loops :) Something like here — agienka
– agienka, Commented Jan 13, 2017 at 11:39

Gordon Linoff · Accepted Answer · 2017-01-13 12:04:23Z

3

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

answered Jan 13, 2017 at 12:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rahul Neekhra Over a year ago

Using CTE you have to use MAXRecusrion set to 0 as default size is 100 when you don't specify maxrecusrion. The upper limit is 32767 sql-server-helper.com/error-messages/msg-310.aspx

Gordon Linoff Over a year ago

@RahulNeekhra . . . That is true. However, I would be surprised if the hierarchy went down 10 levels, much less 100.

Rahul Neekhra Over a year ago

Thanks for comment. Then you can manage. This comment help other users in future

Vadim Loboda · Accepted Answer · 2017-01-13 14:57:56Z

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;

Collectives™ on Stack Overflow

Recursive SQL self join

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related