CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Mark Brittingham
Mark Brittingham

Reputation: 28865

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

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?

Upvotes: 32

Views: 61693

Answers (7)

Matt Tester
Matt Tester

Reputation: 4814

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

Upvotes: 13

Sam ツ
Sam ツ

Reputation: 611

For the future PostgreSQL users, please find the solution below (it is the same as @Matt Tester answered.

SELECT STRING_AGG(cast(id as varchar), ',') from table1
where col1 = 'ABC';

Please note that the cast is required if the column you are selecting is not string (or varchar in database terms).

Upvotes: 3

Max Herold
Max Herold

Reputation: 1

Theres a way to do it without additional functions:

DECLARE @Test nvarchar(max) = ''

SELECT @Test = @Test + ProdID + ', '
FROM Products 
WHERE (ProdType='XYZ')

SELECT @Test

@Test will contain a list of your IDs, although I cannot explain why this works.

Upvotes: 0

Jaxidian
Jaxidian

Reputation: 13511

This is a very old question but I'm adding an answer that applies the already-accepted answer using COALESCE by Justin Niessner. This application is how I would normally want to apply this technique where I'm querying a parent and I want to also have a single column which contains a comma-delimited list of child IDs.

These examples go against an AdventureWorksLT database as created in Azure SQL Database if you use the dropdown to select it when you provision a database. Nothing new here, just a convenient application that might help somebody.

The first query is how I'll normally use it:

SELECT
    SalesLT.ProductCategory.*,
    STUFF((SELECT ','+ cast(ProductID as nvarchar(10)) FROM SalesLT.Product WHERE ProductCategoryID=SalesLT.ProductCategory.ProductCategoryID ORDER BY ProductID FOR XML PATH('')), 1, 1, '') AS ProductIDs
FROM SalesLT.ProductCategory

The second query shows a self-referencing use of it:

SELECT
    ParentCategory.*,
    STUFF((SELECT ','+ cast(child.ProductCategoryID as nvarchar(10)) FROM SalesLT.ProductCategory child WHERE child.ParentProductCategoryID=ParentCategory.ProductCategoryID ORDER BY child.ProductCategoryID FOR XML PATH('')), 1, 1, '') AS ChildCategoryIDs
FROM SalesLT.ProductCategory ParentCategory
WHERE
    EXISTS (SELECT ParentProductCategoryID FROM SalesLT.ProductCategory children WHERE children.ParentProductCategoryID=ParentCategory.ProductCategoryID)

Upvotes: 2

Justin Niessner
Justin Niessner

Reputation: 245399

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

Upvotes: 25

OMG Ponies
OMG Ponies

Reputation: 332561

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

Upvotes: 32

SQLMenace
SQLMenace

Reputation: 134961

For SQL server see here: Concatenate Values From Multiple Rows Into One Column

Upvotes: 1

Related Questions