如何从 SQL 表中提取 ID 列表作为逗号分隔值字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2105729/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I pull a list of ID's from a SQL table as a comma-separated values string?
提问by Mark Brittingham
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# 代码中这样做 - 我希望它通过这种方式直接从数据库中出来。有任何想法吗？
采纳答案by Justin Niessner
In addition to @OMG Ponies method, you could also try this COALESCE trick from:
除了@OMG Ponies 方法，您还可以尝试以下 COALESCE 技巧：
Using COALESCE to Build Comma-Delimited Strings
declare @string nvarchar(255)
select @string = coalesce(@string + ', ', '') + cast(prodid as nvarchar(5))
from products
回答by OMG Ponies
MySQL
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 techniqueson Tim Hall's site.
Tim Hall 的站点上对可用的字符串聚合技术进行了很好的总结。
SQL Server 2005+
SQL Server 2005+
SELECT STUFF((SELECT ','+ t.prodid
FROM PRODUCTS t
WHERE t.prodtype = 'XYZ'
FOR XML PATH('')), 1, 1, '')
回答by Jaxidian
This is a very old question but I'm adding an answer that applies the already-accepted answer using COALESCEby 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.
这是一个非常古老的问题，但我正在添加一个答案，该答案适用于COALESCEJustin Niessner使用的已经接受的答案。这个应用程序是我通常希望在查询父级时应用此技术的方式，并且我还希望有一个包含逗号分隔的子 ID 列表的列。
These examples go against an AdventureWorksLTdatabase 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 数据库中创建的数据库相对应。这里没有什么新东西，只是一个方便的应用程序，可以帮助某人。
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)
回答by Matt Tester
From SQL Server 2017 onwards, you can now use the STRING_AGGfunction.
从 SQL Server 2017 开始，您现在可以使用STRING_AGG函数。
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 语句中创建逗号分隔的列表（因此适用于视图）。鉴于您的示例，它将变为：
SELECT STRING_AGG(ProdID, ',') FROM Products WHERE (ProdType='XYZ');
回答by SQLMenace
For SQL server see here: Concatenate Values From Multiple Rows Into One Column
对于 SQL 服务器，请参见此处：将多行中的值连接到一列
回答by Max Herold
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.
@Test 将包含您的 ID 列表，尽管我无法解释为什么会这样。
