Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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?

like image 720
Mark Brittingham Avatar asked Jan 20 '10 23:01

Mark Brittingham


People also ask

How can I get table values as comma separated in SQL?

In order to fetch the comma separated (delimited) values from the Stored Procedure, you need to make use of a variable with data type and size same as the Output parameter and pass it as Output parameter using OUTPUT keyword.

How do I create a comma separated string in SQL?

— Using STUFF & XML PATH This is the most widely used method to build the comma separated string. In my personal experience, I observed many performance issues related to this method for a large set of data. With SQL Server 2017 release, there is a better way to do this using string_agg built-in function.


2 Answers

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, '') 
like image 93
OMG Ponies Avatar answered Sep 28 '22 06:09

OMG Ponies


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 
like image 21
Justin Niessner Avatar answered Sep 28 '22 06:09

Justin Niessner