May 13, 2013 at 12:26 am
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
May 13, 2013 at 1:39 am
Is it a question ? I see that you are getting what is required form the query.
May 13, 2013 at 5:28 am
Its a solution..:-)
if you want to add more from your side please add it...
May 13, 2013 at 5:51 am
Alas, if only we had "START WITH...CONNECT BY" in our SELECT statement.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply