Dynamic Level value in Hierarchical query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic Level value in Hierarchical query

Author

Topic

arun_1328
Starting Member

2 Posts

Posted - 2009-06-15 : 03:18:54

Consider the following table structure of Employees reporting to their managers in a particular company

EmployeeId ManagerId
1 NULL
2 1
3 1
4 3
5 2
6 2
7 6

Now I want to display the employees who report a particular employee say "2" and their corresponding level with respect to that employee. The output similar to this

EmployeeId Level ManagerId

5 1 2
6 1 2
7 2 6

Can anyone suggest a solution on how to achieve this using WITH Clause query. Thank you.

Arun Vijay.V

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-15 : 03:56:58

[code]DECLARE @Sample TABLE
(
empID INT,
mgrID INT
)

INSERT @Sample
SELECT 1, NULL UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 4, 3 UNION ALL
SELECT 5, 2 UNION ALL
SELECT 6, 2 UNION ALL
SELECT 7, 6

;WITH Yak (empID, mgrID, empPath)
AS (
SELECT empID,
mgrID,
'/' + CAST(empID AS VARCHAR(MAX)) + '/'
FROM @Sample
WHERE mgrID IS NULL

UNION ALL

SELECT s.empID,
s.mgrID,
y.empPath + CAST(s.empID AS VARCHAR(MAX)) + '/'
FROM @Sample AS s
INNER JOIN Yak AS y ON y.empID = s.mgrID
)

SELECT empID AS EmployeeID,
LEN(empPath) - LEN(REPLACE(empPath, '/', '')) - 3 AS [Level],
mgrID AS ManagerID
FROM Yak
WHERE empPath LIKE '%/2/%'
AND empID <> 2
ORDER BY empID[/code]

E 12°55'05.63"
N 56°04'39.26"

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2009-06-15 : 03:58:52

I have give an idea you can change it---

declare @eid int
declare @level int
declare @count int
create table emp(eid int,levels int,mgrid int)

declare ctest cursor
for select eid from test
begin
open ctest
fetch ctest into @eid
while @@FETCH_STATUS=0
begin
declare @mgr int
select @mgr=mgrid from test where eid=@eid
--write inner cursor to find the lvel
if @mgr is null
begin
set @count=1
end
else
begin
set @count=1
while (@mgr is not null)
begin
select @mgr=mgrid from test where eid=@mgr
set @count=@count+1
continue
end
end
insert into emp
select eid,@count as levels,mgrid from test where eid=@eid

fetch ctest into @eid
end

close ctest
deallocate ctest
end

select * from emp
drop table emp

Malay

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-15 : 04:23:52

quote:
Originally posted by malaytech2008

I have give an idea you can change it---

declare @eid int
declare @level int
declare @count int
create table emp(eid int,levels int,mgrid int)

declare ctest cursor
for select eid from test
begin
open ctest
fetch ctest into @eid
while @@FETCH_STATUS=0
begin
declare @mgr int
select @mgr=mgrid from test where eid=@eid
--write inner cursor to find the lvel
if @mgr is null
begin
set @count=1
end
else
begin
set @count=1
while (@mgr is not null)
begin
select @mgr=mgrid from test where eid=@mgr
set @count=@count+1
continue
end
end
insert into emp
select eid,@count as levels,mgrid from test where eid=@eid

fetch ctest into @eid
end

close ctest
deallocate ctest
end

select * from emp
drop table emp

Malay