Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to arrange Employee manager Hierarchy tree in sql server . Expand / Collapse
Author
Message
Posted Monday, May 13, 2013 12:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 11:40 PM
Points: 11, Visits: 53
create table #Sample_emp
(
Ename varchar(50),
EmployeeId int,
ManagerId int
)

insert into #Sample_emp values ('Faisal Husain',11,NULl)
insert into #Sample_emp values ('Deepak Zambre',12,11)
insert into #Sample_emp values ('Milind joshi',13,12)
insert into #Sample_emp values ('Mitesh Oswal',14,13)
insert into #Sample_emp values ('Saket verma',15,13)
insert into #Sample_emp values ('Sagar gadwe',16,13)
insert into #Sample_emp values ('Vinayak Kulkarni',17,13)

select * from #Sample_emp




WITH Asurion_mgr AS (
SELECT

e1.EmployeeId AS [employeeID]
, e1.EmployeeId AS [NewEMPID]
, e1.managerId AS [managerId]
, 0 AS [level]
, CAST(e1.Ename AS VARCHAR(MAX)) AS [Manager Hierarchy]
FROM
#Sample_emp e1


UNION ALL

SELECT

Am.[employeeId]
, e2.EmployeeId AS [EmployeeId]
, e2.managerID AS [managerId]
, Am.[level] + 1 AS [level]
, CAST(Am.[Manager Hierarchy] + ' - ' + e2.[Ename] AS VARCHAR(MAX)) AS [Manager Hierarchy2]
FROM
Asurion_mgr Am
JOIN #Sample_emp e2
ON e2.EmployeeId = Am.[managerId]
WHERE
e2.EmployeeId <> Am.[employeeID]

)
SELECT [employeeID],[Manager Hierarchy] FROM Asurion_mgr where managerId is null
Post #1451981
Posted Monday, May 13, 2013 1:39 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:04 AM
Points: 3,517, Visits: 2,606
Is it a question ? I see that you are getting what is required form the query.
Post #1451990
Posted Monday, May 13, 2013 5:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 11:40 PM
Points: 11, Visits: 53
Its a solution..

if you want to add more from your side please add it...
Post #1452051
Posted Monday, May 13, 2013 5:51 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:37 PM
Points: 3,305, Visits: 2,351
Alas, if only we had "START WITH...CONNECT BY" in our SELECT statement.


Tally Tables - Performance Personified
Best practices on how to ask questions
Post #1452061
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse