如何从SQL表中提取ID列表作为逗号分隔值字符串?

I have to pull a list of integer IDs from a table using only records that match some criteria. For example:

我必须从表中提取一个整数id列表，只使用符合某些条件的记录。例如:

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?

而不是作为记录。我不想在c#代码中这样做——我希望它通过这样的查询直接从数据库中出来。什么好主意吗?

4 个解决方案

#1

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

除了@OMG小马的方法，你也可以试试这个联合技巧:

Using COALESCE to Build Comma-Delimited Strings

使用合并来构建以逗号分隔的字符串。

declare @string nvarchar(255)

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

#2

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.

在Tim Hall的网站上有一个很好的总结可用的字符串聚合技术。

SQL Server 2005+

---

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

#3

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

对于SQL server，请参见这里:将多个行中的值连接到一个列中

#4

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.

这是一个非常古老的问题，但是我添加了一个答案，使用Justin Niessner的COALESCE提供的已经被接受的答案。这个应用程序是我通常希望应用这种技术的方式，在这种情况下，我要查询父类，并且还要有一个包含逗号分隔的子类id列表的列。

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.

如果您在提供数据库时使用下拉菜单选择AdventureWorksLT数据库，那么这些示例将与在Azure SQL数据库中创建的AdventureWorksLT数据库背道而驰。这里没有什么新东西，只是一个方便的应用程序，可能会对某些人有所帮助。

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)