Child data field aggregated into a single field, separated by commas

Question

I work in SQL Server 2008 R2

I have a parent table and a child table (one to many relationships). What I would like to do is query the parent table, and also include a field from the child table, aggregated into a comma separated row.

So, if the parent table is called Person with the identifier field, FirstName and LastName, and the child table is called the child and child tables, the ParentID field (FK for the parent table), FirstName and LastName ... I would like to return a dataset like this:

ParentFirstName = Mike
ParentLastName = Davis
KidsFirstNames = Susie, David, Alex

I thought there was a way to do this using the XML path, but I can't remember the syntax.

Thanks in advance.

Answer 1

Take a look at this example.

declare @YourTable table (BirthDay datetime, PersonName varchar(20)) insert into @YourTable VALUES ('1-10-2010', 'Joe' ) insert into @YourTable VALUES ('2-10-2010', 'Bob' ) insert into @YourTable VALUES ('2-10-2010', 'Alice') SELECT p1.BirthDay ,STUFF( (SELECT ', ' + p2.PersonName FROM @YourTable p2 WHERE p2.BirthDay=p1.BirthDay ORDER BY p2.PersonName FOR XML PATH(''), TYPE ).value('.','varchar(max)') ,1,2, '' ) AS PersonNames FROM @YourTable p1 GROUP BY p1.BirthDay