Which supervisor do I have?
In this exercise, we want to get the path from the boss at the top of the hierarchy, to the employees at the bottom of the hierarchy. For this task, we have to combine the information obtained in each step into one field. You can do this by combining the ID
s using CAST()
from number to string. An example is CAST(ID AS VARCHAR(MAX))
to convert ID
of type number to type char.
The task is now to find the path for employees Chris Feierabend with ID=18
and Jasmin Mentil with ID=16
all the way to the top of the organization. The output should look like this: boss_first_level -> boss_second_level ...
. The IDs of the employees and not their names should be selected.
This exercise is part of the course
Hierarchical and Recursive Queries in SQL Server
Exercise instructions
- Initialize
Path
to theID
of the supervisor (0
) and the start condition of the recursion. UNION
the anchor member and define the recursive member fields (ID
,Name
,Supervisor
).- Add the the ID of the supervisor
Supervisor
to thePath
in each step. - Select the IDs of employees
Chris Feierabend
andJasmin Mentil
in the CTE.
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 Path with CAST
___('___' AS VARCHAR(MAX)) as Path
FROM employee
WHERE Supervisor = 0
-- UNION the anchor query
___ ALL
-- Select the recursive query fields
SELECT
emp.___,
emp.___,
emp.___,
-- Add the supervisor in each step. CAST the supervisor.
Path + '->' + ___(emp.___ AS VARCHAR(MAX))
FROM employee emp
INNER JOIN employee_Hierarchy
ON emp.Supervisor = employee_Hierarchy.ID
)
SELECT Path
FROM employee_Hierarchy
-- Select the employees Chris Feierabend and Jasmin Mentil
WHERE ___ = 16 OR ID = ___;