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

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)

Edit (ten years later):

SQL Server 2017 introduced the STRING_AGG() function which provides an official way of concatenating strings from different rows. Like other aggregation functions such as COUNT(), it can be used with GROUP BY.

So for the example above you could do:

select string_agg(value, ', ')
from simpleTable

If you had some other column and you wanted to concatenate for values of that column, you would add a 'group by' clause, e.g:

select someCategory, string_agg(value, ', ') as concatValues
from simpleTable
group by someCategory

Note string_agg will only work with SQL 2017 and above.

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 ('')

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