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

View Course

Exercise instructions

Set the ParentID of 290 as starting point.
If Parent.ID = '' in the CASE operation, the Parent field needs to be set to the current ParentID.
If Parent.ID <> '' in the CASE operation, the Parent ID needs to be added to the current Parent for each iteration.
Select the Name and Parent 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 ___;

Edit and Run Code