Introduction
The very basics
Infinite looping
Processing trees with recursive CTEs
25. Additional exercise – trees
Processing graphs with recursive ctes
Summary

Instruction

Let's move on to a more interesting exercise!

Exercise

Your task is to show the following columns:

  • Id – the ID of each department,
  • Name – the name of the department,
  • PartOf – the ID of the department one level up,
  • Path – this is defined as all department names, from the root department until the given department, separated with slashes (N'/').

Remember the root department has a NULL PartOf ID.

Stuck? Here's a hint!

The query we created for employees might help you here:

WITH Hierarchy AS (
  SELECT
    Id,
    FirstName,
    LastName,
    SuperiorId,
    CAST(LastName AS NVARCHAR(MAX)) AS Path
  FROM Employee
  WHERE SuperiorId IS NULL
  UNION ALL
  SELECT
    Employee.Id,
    Employee.FirstName,
    Employee.LastName,
    Employee.SuperiorId,
    Hierarchy.Path + N'->' + Employee.LastName
  FROM Employee
  INNER JOIN Hierarchy
    ON Employee.SuperiorId = Hierarchy.Id
)

SELECT
  *
FROM Hierarchy;