1. Learn
    2. /
  2. Courses
    3. /
  3. Hierarchical and Recursive Queries in SQL Server
Connected

Exercise

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.

Instructions

100 XP
  • 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.