SQL: Concatenate Fields From Multiple Rows In SQL

In SQL Server 2005 and up you can use the following SQL code to concatenate fields from multiple rows.

Sample Data:

declare @YourTable table
(
    RowID int,
    HeaderValue int,
    ChildValue varchar(5)
)

insert into @YourTable values (1, 1, 'CCC')
insert into @YourTable values (2, 2, 'BBB')
insert into @YourTable values (3, 2, 'AAA')
insert into @YourTable values (4, 3, '123')
insert into @YourTable values (5, 3, 'A & Z')

RowID HeaderValue ChildValue
1     1           CCC
2     2           BBB
3     2           AAA
4     3           123
5     3           A & Z

The Concatenation Code:

set nocount off

select
    t1.HeaderValue,
stuff(
        (select ', ' -- delimiter
           + t2.ChildValue
         from @YourTable t2
         where t1.HeaderValue=t2.HeaderValue
         order by t2.ChildValue
         for xml path(''), TYPE).value('.','varchar(max)'),
       1, 2, '') as ChildValues
from @YourTable t1
group by t1.HeaderValue

Output:

HeaderValue ChildValues
----------- -------------------
1           CCC
2           AAA, BBB
3           123, A & Z