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

Question

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?

Answer 1

MySQL

---

SELECT GROUP_CONCAT(t.prodid SEPARATOR ',')
  FROM PRODUCTS t
 WHERE t.prodtype = 'XYZ'

Oracle:

---

There is an excellent summary of the available string aggregation techniques on Tim Hall's site.

SQL Server 2005+

---

SELECT STUFF((SELECT ','+ t.prodid
                FROM PRODUCTS t
               WHERE t.prodtype = 'XYZ'
             FOR XML PATH('')), 1, 1, '')