Hierarchical Report - SQL Server Forums

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

Hierarchical Report

Author

Topic

sanjay5219
Posting Yak Master

240 Posts

Posted - 2010-04-23 : 14:05:22

Dear All,

I have one table T1 with three columns

1. Employee code
2. Employee Name
3. Team Lead

Employee_code Employee_Name Team_Lead-Id
E1 E1 E1
E2 E2 E1
E3 E3 E1
E9 E4 E3
E10 A10 E4
E11 A11 E4
E12 A12 E4
E13 A13 E4
E14 A14 E4
E15 A15 E4
E16 A16 E4
E17 A17 E4
E18 A18 E4
E19 A19 E4
E20 A20 E4

Now I want to run a query where I should see the hierarchical result for example if I have created the procedure and passed the parameter E4 then 10 employees should come and if I passed E3 then I should get 11 people because based on hierarchy E$ reports to E3.That means when I passed E1 as a parameter I should get everybody based on hierarchy.

I am struggling with this please suggest

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 14:08:02

are you using sql 2005? if yes,see below
http://msdn.microsoft.com/en-us/library/ms186243.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sanjay5219
Posting Yak Master

240 Posts

Posted - 2010-04-26 : 15:24:39

No I am using SQL 2000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 00:18:05

create a procedure like this



CREATE PROC GetHierarchy
@Emp varchar(100)
AS
CREATE TABLE #Temp
(
Child varchar(10),
Parent varchar(10),
Level int
)
DECLARE @Level int
SET @Level=0

INSERT INTO #Temp
SELECT Employee_Name,
Team_Lead_Id,@Level
FROM #Test
WHERE Employee_Name=@Emp

WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level +1
--SELECT * FROM #Temp
INSERT INTO #Temp
SELECT t.Employee_Name,t.Team_Lead_Id,@Level
FROM #Test t
INNER JOIN #Temp tmp
ON tmp.Child = t.Team_Lead_Id
AND tmp.Level=@level-1
LEFT JOIN #Temp tmp1
ON tmp1.Child = t.Employee_Name
AND tmp1.Parent = t.Team_Lead_Id
AND tmp1.Level = @Level-1
WHERE tmp1.Child IS NULL
END

SELECT * FROM #Temp
DROP TABLE #Temp
GO

then call it like:-

CREATE TABLE #test

(
Employee_code varchar(10),
Employee_Name varchar(100),
Team_Lead_Id varchar(10)
)

INSERT #Test
VALUES('E1', 'E1', 'E1'),
('E2', 'E2', 'E1'),
('E3', 'E3', 'E1'),
('E9', 'E4', 'E3'),
('E10', 'A10', 'E4'),
('E11', 'A11', 'E4'),
('E12' ,'A12', 'E4'),
('E13' ,'A13', 'E4'),
('E14' ,'A14', 'E4'),
('E15' ,'A15' ,'E4'),
('E16', 'A16', 'E4'),
('E17', 'A17', 'E4'),
('E18' ,'A18' ,'E4'),
('E19' ,'A19' ,'E4'),
('E20' ,'A20' ,'E4')


EXEC  GetHierarchy 'E4'


output
----------------------------------

Child	Parent	Level
E4	E3	0
A10	E4	1
A11	E4	1
A12	E4	1
A13	E4	1
A14	E4	1
A15	E4	1
A16	E4	1
A17	E4	1
A18	E4	1
A19	E4	1
A20	E4	1

in actual case replace #test with your actual table everywhere

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources