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
Post a Comment