1. Learn
    2. /
  2. Courses
    3. /
  3. Hierarchical and Recursive Queries in SQL Server

Exercise

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 IDs 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.

Instructions

100 XP
  • Initialize Path to the ID 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 the Path in each step.
  • Select the IDs of employees Chris Feierabend and Jasmin Mentil in the CTE.