Get child records on one column

sql, sql-server, sql-server-2005

Solution

select P.ID,
       P.Name,
       stuff((select ','+cast(S.ID as varchar(10))
              from Son S
              where S.ParentID = P.ID
              for xml path('')), 1, 1, '') AllMySons
from Parent P

SE-Data

Problem

I have a table parent and a table son like this: ``` Parent ID Name 1 Parent1 2 Parent2 Son ID ParentID 10 1 20 1 30 1 15 2 25 2 ``` what's the easiest way to, when selecting the parent, get all the ID's of the sons on a single column? Like this: ``` Result: ID Name AllMySons 1 Parent1 10,20,30 2 Parent2 15, 25 ``` I thought of building a function to generate the string with the sons but does anyone have a better idea?

Original source