Create a comma separated string

I just used this again after 2 years so though I should flesh out the description a bit.

This is really useful if you need to quickly create a delimited string based on a GROUP BY statement.

The example below illustrates this using a simple set of sample data.

select 1 as id, ‘frog’ as name into #tmp
insert into #tmp (id, name) values (1,’toad’)
insert into #tmp (id, name) values (2,’cat’)
insert into #tmp (id, name) values (2,’dog’)

SELECT p1.Id, ( SELECT Name + ‘,’ FROM #tmp p2
WHERE p2.Id = p1.Id ORDER BY Name FOR XML PATH(”) ) AS list
FROM #tmp p1 GROUP BY Id ;