Let's move on to a more interesting 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.
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;
