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.

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.

Exercise

Get all possible parents in one field?

Instructions