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.

A section of the entire hierarchy

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

View Course

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 the IDs 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;

Edit and Run Code