How can I pull a list of ID's from a SQL table as a comma-separated values string?

sql, sql-server, sql-server-2008

Solution

In addition to @OMG Ponies method, you could also try this COALESCE trick from:

Using COALESCE to Build Comma-Delimited Strings

declare @string nvarchar(255)

select @string = coalesce(@string + ', ', '') + cast(prodid as nvarchar(5))
from products

Problem

I have to pull a list of integer IDs from a table using only records that match some criteria. For example: ``` Select ProdID From Products Where (ProdType='XYZ'); ``` The catch is that I have to return it as a set of comma separated values so I can use it to select items in a multi-select list: ``` 111,231,554,112 ``` rather than as records. I do not want to do this in my C# code - I'd like it to come right out of the database via a query this way. Any ideas?

Original source