SQL - Recursive Queries by CTE

CTE (Common Table Expression) is very useful when we want to perform recursion by defining a self referencing query. In Northwind Database sample (downloadable from CodePlex web pages) Employees table has a column with title of ReportsTo which refers to EmployeeID of another Employee record in current Employee row, it's the manager. The exception is for the managers who their ReportsTo column is NULL (They are lucky people who don't need to report to any body, watch out, it shows you are responsible not them!). Then the managers are sitting in Level 1 and employees are in next levels. Then employees report to their manager who are in upper level. It shows the table has a reference to itself, it's a join back that is possible to implement by utilizing power of recursive connection.

To cover all the sub levels and show Employees and their managers, we need to UNION result set of multi levels all together and go deep inside till find all requested info. Each time that the query needs to go one level deeper, the Level column adds by 1 to show the correct level of employee in organization diagram.

WITH Emp (EmpID, EmpName, ManagerID,
          ManagerName, [EmployeeLevel] )
AS
(
   SELECT EmpTbl.EmployeeID,
          EmpTbl.LastName,
          EmpTbl.ReportsTo,
          EmpTbl.FirstName+SPACE(1)+EmpTbl.LastName, 1
   FROM [Northwind].[dbo].[Employees] as EmpTbl
   WHERE ReportsTo IS NULL
   UNION ALL
      SELECT EmpTbl.EmployeeID,
             EmpTbl.LastName,
             EmpTbl.ReportsTo,
             Mgr.FirstName+SPACE(1)+Mgr.LastName,
             [EmployeeLevel] + 1
      FROM [Northwind].[dbo].[Employees] as EmpTbl
    JOIN Emp
         ON Emp.EmpID = EmpTbl.ReportsTO
      JOIN [Northwind].[dbo].[Employees] as Mgr
         ON EmpTbl.ReportsTO = Mgr.EmployeeID
)
SELECT EmpID,
       EmpName,
       ManagerID,
       ManagerName,
       [EmployeeLevel]
FROM Emp

Output:

2   Fuller      NULL   Andrew Fuller     1
1   Davolio     2      Andrew Fuller     2
3   Leverling   2      Andrew Fuller     2
4   Peacock     2      Andrew Fuller     2
5   Buchanan    2      Andrew Fuller     2
8   Callahan    2      Andrew Fuller     2
6   Suyama      5      Steven Buchanan   3
7   King        5      Steven Buchanan   3
9   Dodsworth   5      Steven Buchanan   3

SQL - Recursive Queries by CTE