SQL: Print employee hierarchy tree

Given a table that contains list of employees and their managers, print the hierarchy tree for a given employee.

You can use Recursive Queries Using Common Table Expressions:  http://msdn.microsoft.com/en-us/library/ms186243.aspx

Another way to go is using While loop:

create table Employess (Id int primary key, Name varchar(255), managerId int foreign key references Employess(Id));
insert into Employess (Id, Name, managerId) Values (1, 'TopManager', null);
insert into Employess (Id, Name, managerId) Values (2, 'Manager', 1);
insert into Employess (Id, Name, managerId) Values (3, 'Lead01', 2);
insert into Employess (Id, Name, managerId) Values (4, 'Lead02', 2);
insert into Employess (Id, Name, managerId) Values (5, 'Worker01', 3);
insert into Employess (Id, Name, managerId) Values (6, 'Worker02', 4);
insert into Employess (Id, Name, managerId) Values (7, 'Worker03', 4);

declare @managerId int;
declare @employeeName varchar(256);

select @employeeName = 'Worker01';

create table EmployeeHierarchy (Name varchar(256), Level int);

declare @count int;
select @count = 0;
select @managerId = (select managerId from Employess where Name=@employeeName)
WHILE (@managerId <> 0)
Begin
insert into EmployeeHierarchy (Name, Level) Values (@employeeName, @count)

select @count = @count + 1;
select @employeeName = (select Name from Employess where Id=@managerId);
select @managerId = (select managerId from Employess where Name=@employeeName)

End

select * from EmployeeHierarchy

drop table EmployeeHierarchy;
drop table Employess;






Comments