Common Table Expressions (CTEs) are a powerful feature in SQL Server that allow you to define temporary result sets that can be referenced within a query. One of the most interesting use cases of CTEs is building hierarchical queries using recursive CTEs.
Let’s consider an example to understand how recursive CTEs work. Suppose we have an employee table with the following data:
| EmpID | FirstName | LastName | MgrID |
|---|---|---|---|
| 1 | David | Kennson | 11 |
| 2 | Eric | Bender | 11 |
| 3 | Lisa | Kendall | 4 |
| 4 | David | Lonning | 11 |
| 5 | John | Marshbank | 4 |
| 6 | James | Newton | 3 |
| 7 | Sally | Smith | NULL |
We want to write a recursive CTE that shows the EmpID, FirstName, LastName, MgrID, and employee level. The CEO should be listed at Level 1. All people who work for the CEO will be listed at Level 2. All of the people who work for those people will be listed at Level 3.
To achieve this, we can use the following recursive CTE:
WITH EmpList AS (
SELECT Boss.EmpID, Boss.FirstName, Boss.LastName, Boss.MgrID, 1 AS Level
FROM Employee AS Boss
WHERE Boss.MgrID IS NULL
UNION ALL
SELECT E.EmpID, E.FirstName, E.LastName, E.MgrID, EmpList.Level + 1
FROM Employee AS E
INNER JOIN EmpList ON E.MgrID = EmpList.EmpID
)
SELECT *
FROM EmpList
This query starts with the CEO (where MgrID is NULL) and recursively joins with the employees who report to them. The Level column keeps track of the hierarchy level.
Another common use case of recursive CTEs is to produce a list of employees and their managers, along with the employee’s level in the hierarchy. Here’s an example:
WITH EmployeeList (EmployeeID, FullName, ManagerName, Level) AS (
SELECT EmployeeID, FullName, '' AS ManagerID, 1 AS Level
FROM Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT emp.EmployeeID, emp.FullName, mgr.FullName, mgr.Level + 1
FROM EmployeeList mgr
JOIN Employee emp ON emp.ManagerID = mgr.EmployeeID
)
SELECT EmployeeID, FullName, ManagerName, Level
FROM EmployeeList
In this query, we start with the employees who don’t have a manager (where ManagerID is NULL) and recursively join with their managers. The Level column is incremented by 1 for each level in the hierarchy.
By understanding and utilizing recursive CTEs, you can easily build complex hierarchical queries in SQL Server. These queries are useful in scenarios where you need to analyze organizational structures, reporting hierarchies, or any other data with a hierarchical relationship.
So, the next time you encounter a hierarchical data structure in SQL Server, remember to leverage the power of recursive CTEs to simplify your queries and get the desired results.