Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Recursive Queries in MS SQL Server

Introduction
Infinite looping
Processing trees with recursive CTEs
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;