Get all possible parents in one field?
Your final task in this chapter is to find all possible parents starting from one ID and combine the IDs of all found generations into one field.
To do this, you will search recursively for all possible members and add this information to one field. You have to use the CAST()
operator to combine ID
s into a string.
You will search for all family members starting from ID = 290
. In total there are 300 entries in the table family
.
This exercise is part of the course
Hierarchical and Recursive Queries in SQL Server
Exercise instructions
- Set the
ParentID
of 290 as starting point. - If
Parent.ID = ''
in theCASE
operation, theParent
field needs to be set to the currentParentID
. - If
Parent.ID <> ''
in theCASE
operation, the Parent ID needs to be added to the current Parent for each iteration. - Select the
Name
andParent
from the defined CTE.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
WITH tree AS (
SELECT
ID,
Name,
ParentID,
CAST('0' AS VARCHAR(MAX)) as Parent
FROM family
-- Initialize the ParentID to 290
WHERE ___ = ___
UNION ALL
SELECT
Next.ID,
Next.Name,
Parent.ID,
CAST(CASE WHEN Parent.ID = ''
-- Set the Parent field to the current ParentID
THEN(CAST(Next.___ AS VARCHAR(MAX)))
-- Add the ParentID to the current Parent in each iteration
ELSE(Parent.Parent + ' -> ' + CAST(Next.___ AS VARCHAR(MAX)))
END AS VARCHAR(MAX))
FROM family AS Next
INNER JOIN tree AS Parent
ON Next.ParentID = Parent.ID)
-- Select the Name, Parent from tree
SELECT ___, ___
FROM ___;