Concatenating multiple rows as a single column in a row in SQL Server 2005/2008 (TSQL)

Assuming we have a one to many table, and we need to list all children items per parent in a single row, here is a quick way of doing it in SQL Server 2005/2008 (TSQL)

The following will concatenate all children names belonging to the parent with a ',' delimiter.

 SELECT DISTINCT parent.id, parent.name, CA, CB
 FROM ParentTable parent
 CROSS APPLY
 (
   SELECT  child.name + ',' 
  FROM ChildA child
  WHERE child.parentId = parent.id
                ORDER BY child.name
  FOR XML PATH('')
 ) childA(CA)
 CROSS APPLY
 (
   SELECT  child2.name + ',' 
  FROM ChildB child2
  WHERE child2.parentId = parent.id
                ORDER BY child2.name
  FOR XML PATH('')
 ) childB(CB)