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

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

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

From SQL Server 2017 onwards, you can now use the STRING_AGG function.

This allows you to create the comma-separated list from within the SELECT statement (so works nicely with views). Given your example, it will become:

SELECT STRING_AGG(ProdID, ',') FROM Products WHERE (ProdType='XYZ');