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)

Hierarchy Table SQL Statement using CTE (Common Table Expressions)

Assuming a table with fields id, name, parent_id where parent_id is self referencing.

We can use the T-SQL Common Table Expression to easily loop through all children items.

Example Table:
(categories)
id | name | parent_id
1 | CatA | NULL
2 | CatB | 1
3 | CatC | 1
4 | CatD | 2
5 | CatE | 3

 -- This will select all children belonging to CatA where id = 1
;With Hierarchy 
As
(
    SELECT    id, name, parent_id
    FROM      categories
    WHERE     id = 1 -- CatA Id
    UNION  ALL
    SELECT    child.id, child.name, child.parent_id
    FROM      categories child
    INNER JOIN  Hierarchy parent on child.parent_id = parent.id
)
-- This will select all children from the Hierarchy
Select * from Hierarchy