how to get the hierarachy - 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

SQL Server 2005 Forums

Transact-SQL (2005)

how to get the hierarachy

Author

Topic

sqldev6363
Yak Posting Veteran

54 Posts

Posted - 2008-09-02 : 10:43:09

hi,

i have a table contains the id,employee,managerID. i will give the scenario.

ID Employee ManagerID
1 A 4
2 B 2
3 C 1
4 D 2
5 E 4

I need to get the employee hierarchy for the above scenario.For A the Manger is 4 and For B, 2, like that i have so many records.I need to find for each employee who is the manager.

Can any one help me out

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 10:50:46

Yes. See Books Online.
There are examples of RECURSIVE CTE.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 10:57:26

Or, if your query is not about traversing all employee to get "top boss" and you only want "nearest" manager, try this

DECLARE	@Sample TABLE
	(
		ID INT,
		Employee CHAR(1),
		ManagerID INT
	)

INSERT	@Sample
SELECT	1, 'A', 4 UNION ALL
SELECT	2, 'B', 2 UNION ALL
SELECT	3, 'C', 1 UNION ALL
SELECT	4, 'D', 2 UNION ALL
SELECT	5, 'E', 4

SELECT		m.ID AS ManagerID,
		m.Employee AS ManagerName,
		e.ID,
		e.Employee
FROM		@Sample AS e
INNER JOIN	@Sample AS m ON m.ID = e.ManagerID
ORDER BY	m.ID,
		e.ID

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources