Get the hierarchy position
An important problem when dealing with recursion is tracking the level of recursion. In the IT organization, this means keeping track of the position in the hierarchy of each employee.
For this, you will use a LEVEL
field which keeps track of the current recursion step. You have to introduce the field in the anchor member, and increment this value on each recursion step in the recursion member.
Keep in mind, the first hierarchy level is 0, the second level is 1 and so on.
This exercise is part of the course
Hierarchical and Recursive Queries in SQL Server
Exercise instructions
- Initialize the field
LEVEL
to 1 at the start of the recursion. - Select the information of the IT director as the initial step of the CTE by filtering on
Supervisor
. - Set
LEVEL
to the current recursion step. - Perform a
JOIN
with the defined CTE on theID
s of the supervisor and the employee.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
WITH employee_hierarchy AS (
SELECT
ID,
NAME,
Supervisor,
-- Initialize the field LEVEL
___ as LEVEL
FROM employee
-- Start with the supervisor ID of the IT Director
WHERE ___ = ___
UNION ALL
SELECT
emp.ID,
emp.NAME,
emp.Supervisor,
-- Increment LEVEL by 1 each step
___ + 1
FROM employee emp
JOIN employee_hierarchy
-- JOIN on supervisor and ID
ON emp.___ = employee_hierarchy.___)
SELECT
cte.Name, cte.Level,
emp.Name as ManagerID
FROM employee_hierarchy as cte
JOIN employee as emp
ON cte.Supervisor = emp.ID
ORDER BY Level;