What is the best way to collapse the rows of a SELECT into a string?

Question

In a SQL statement ( or procedure ) I want to collapse the rows of this table into a single comma delimited string.

simpleTable

id  value
--  ----- 
1    "a"
2    "b"
3    "c"

Collapse to:

"a, b, c"

Answer 1

You can concatenate using an embedded 'set' statement in a query:

declare @combined varchar(2000)
select @combined = isnull(@combined + ', ','') + isnull(value,'')
from simpleTable

print @combined

(Note that the first isnull() initialises the string, and the second isnull() is especially important if there's any chance of nulls in the 'value' column, because otherwise a single null could wipe out the whole concatenation)

(edited code and explanation after comments)

Answer 2

DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 
   CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @EmployeeList

Results:

1, 2, 4

Answer 3

This will only work in MSSQL 2005+

select value + ',' from simpletable for xml path ('')

..one way to prevent the extra comma:

select case(row_number() over (order by id))
when 1 then value else ',' + value end
from simpletable
for xml path ('')

Answer 4

This is based on @codeulike answer, but will prevent losing the portion of the string that gets concatenated before a null "value" is concatenated on.

declare @combined varchar(2000)
select @combined = isnull(@combined + ', ','') + ISNULL(value,'')
from simpleTable

print @combined