having such a table in a MS SQL Server 2005

where the table represents some employees and their tree structure in the organization, where each employee has a foreign key “ManagerID” which is  the ID of another Employee in the same table, and this table is represented with the following tree

the problem is that i want to select the employees with an additional field holding their level in the previous tree

a recursive solution, huh !!

ok, that can be solved using recursive SQL function but here is another approach… just SQL select statement that uses CTE “Common Table Expressions” introduced in MS SQL Server 2005 -yeah i know i’m too late😦 –

so here is the SQL Select Query

WITH t AS (
--select the root column(s)
--The anchor member
SELECT     Employee.EmpID, Employee.ManagerID, Employee.Name, 0 AS LEVEL
 FROM         Employee
 WHERE     ManagerID IS NULL

 UNION ALL
--select the children recursively
--The recursive member
 SELECT     Employee.EmpID, Employee.ManagerID, Employee.Name, LEVEL + 1 AS LEVEL
 FROM         Employee JOIN t ON t .EmpID = Employee.ManagerID
)
 SELECT     * FROM t

the term “With t as” is the CTE which allows us to create a table on the fly named t, where this table is filled using the query between the preceding brackets.

as any normal recursion, there must be a starting point and a terminating condition

the starting point here is the Anchor Member which is the first select query that gets the Employees whose “ManagerID” is NULL “they are the managers”

then the statement “Union All” that applies the next query over and over till it returns nothing “which is the terminating condition here”

and the recursive member here uses the selected rows in the table “t” till the moment to join on the ManagerID in the Employee table with the ID of the already selected Managers in t in the last execution of the recursive member

running such query on the table “Employee” will produce the following result set

i hope that helps…

and here is my reference