Recursive SQL - 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

Recursive SQL

Author

Topic

jallah_88
Starting Member

12 Posts

Posted - 2011-03-15 : 05:42:06

Hi All,

I have some difficulties showing the results of my Recursive SQL Query. Here's the Query:
WITH Managers (Id, EmployeeName, EmployeesManagerId, ManagerLevel) AS
(
SELECT ID, EmployeeName, EmployeesManagerId, 1 as ManagerLevel
FROM Employee
WHERE EmployeesManagerId IS NULL
UNION ALL
SELECT e.ID,e.EmployeeName, e.EmployeesManagerId, ManagerLevel + 1 as ManagerLevel
FROM Employee e INNER JOIN Managers m
ON e.EmployeesManagerId = m.ID
)
SELECT * FROM Managers ORDER BY EmployeesManagerId

And the results:
Id EmployeeName EmployeesManagerId ManagerLevel
1 anne NULL 1
2 ole 1 2
3 per 1 2
4 sanne 1 2
5 kurt 2 3
6 poul 2 3
10 lotte 4 3
11 lars 4 3
12 vivi 4 3
7 kamma 5 4
8 hans 5 4
9 irene 5 4

Where EmployeesManagerId refers to each Employees Id.

Thing is I want to show it as follows:
Id EmployeeName EmployeesManagerId ManagerLevel
1 anne NULL 1
2 ole 1 2
5 kurt 2 3
7 kamma 5 4
8 hans 5 4
9 irene 5 4
6 poul 2 3
3 per 1 2
4 sanne 1 2
10 lotte 4 3
11 lars 4 3
12 vivi 4 3

Hope you can help me :)

jallah_88
Starting Member

12 Posts

Posted - 2011-03-15 : 05:49:40

Sorry it looked more keen to the eye when i wrote it. I want it to show as more tree like structure.
Boss
Vice Boss
Vice Boss' assistant 1
Vice Boss' assistants assistant
Vice Boss' assistant 2
Second Vice Boss
Second Vice Boss' assistant 1

etc..

Hope you get the picture as it is quite difficult for me to explain :)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-15 : 05:52:15

So it's not the recursiveness that's the problem, it's the ordering of the output...? In that case you need to add some additional metadata (notice the TreePath):

WITH Managers (Id, EmployeeName, EmployeesManagerId, ManagerLevel, TreePath) AS
(
	SELECT ID, EmployeeName, EmployeesManagerId, 1 as ManagerLevel, 
		'\' + CAST(ID AS varchar(max)) as TreePath
	FROM Employee
	WHERE EmployeesManagerId IS NULL

	UNION ALL
	
	SELECT e.ID, e.EmployeeName, e.EmployeesManagerId, ManagerLevel + 1 as ManagerLevel,
		m.TreePath + '\' + CAST(e.ID AS varchar(max))
	FROM Employee e 
		INNER JOIN Managers m 
			ON e.EmployeesManagerId = m.ID
)
SELECT * 
FROM Managers 
ORDER BY TreePath

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

jallah_88
Starting Member

12 Posts

Posted - 2011-03-15 : 06:02:24

Nice! Brilliant way to deal with it. Thanks a lot Lumbago :)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-15 : 06:20:01

Great that it worked out for you. This TreePath can actually be directly converted in to the hierarchyid datatype in SQL Server 2008 (this is in fact how you create it) and it is very useful if you i.e. only want to display parts of a tree structure, move nodes around the tree, etc.

-> http://msdn.microsoft.com/en-us/library/bb677212.aspx

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

jallah_88
Starting Member

12 Posts

Posted - 2011-03-15 : 09:07:14

Thanks, your link was helpful to understand what your given code actually did in the query :)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-15 : 10:01:12

:)

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources