0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for administrators, end users, developers and designers for Craft CMS

declare @employee table (
  ID int not null primary key,
  Name nvarchar(100) not null,
  ManagerID int null
)

insert @employee (ID, Name, ManagerID) values (1, 'President', null)
insert @employee (ID, Name, ManagerID) values (2, 'VP 1', 1)
insert @employee (ID, Name, ManagerID) values (3, 'Manager 1', 2)
insert @employee (ID, Name, ManagerID) values (4, 'Manager 2', 9)
insert @employee (ID, Name, ManagerID) values (5, 'Employee 1', 3)
insert @employee (ID, Name, ManagerID) values (6, 'Employee 2', 4)
insert @employee (ID, Name, ManagerID) values (7, 'Employee 3', 9)
insert @employee (ID, Name, ManagerID) values (8, 'Employee 4', 9)
insert @employee (ID, Name, ManagerID) values (9, 'VP 2', 1)

;with employee_list as (
  select ID, Name, ManagerID, cast(NULL as nvarchar(100)) as ManagerName from @employee where ManagerID is null
  union all
  select e.ID, e.Name, e.ManagerID, manager.Name as ManagerName from @employee e inner join employee_list manager on manager.ID = e.ManagerID
)

select * from employee_list

fork query

edited jun 22 18

created jun 22 18 anonymous