如何使用 SQL 查询创建逗号分隔的列表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1817985/
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 do I create a comma-separated list using a SQL query?
提问by leora
I have 3 tables called:
我有 3 个表称为：
- Applications (id, name)
- Resources (id, name)
- ApplicationsResources (id, app_id, resource_id)
- 应用程序（ID，名称）
- 资源（ID，名称）
- 应用程序资源（id、app_id、resource_id）
I want to show on a GUI a table of all resource names. In one cell in each row I would like to list out all of the applications (comma separated) of that resource.
我想在 GUI 上显示所有资源名称的表格。在每一行的一个单元格中，我想列出该资源的所有应用程序（逗号分隔）。
So the question is, what is the best way to do this in SQL as I need to get all resources and I also need to get all applications for each resource?
所以问题是，在 SQL 中执行此操作的最佳方法是什么，因为我需要获取所有资源，并且还需要获取每个资源的所有应用程序？
Do I run a select * from resources first and then loop through each resource and do a separate query per resource to get the list of applications for that resource?
我是否首先运行 select * from resources 然后遍历每个资源并对每个资源执行单独的查询以获取该资源的应用程序列表？
Is there a way I can do this in one query?
有没有一种方法可以在一个查询中做到这一点？
采纳答案by Dmytrii Nagirniak
There is no way to do it in a DB-agnostic way. So you need to get the whole data-set like this:
没有办法以数据库不可知的方式做到这一点。所以你需要像这样获取整个数据集：
select
r.name as ResName,
a.name as AppName
from
Resouces as r,
Applications as a,
ApplicationsResources as ar
where
ar.app_id = a.id
and ar.resource_id = r.id
And then concat the AppNameprogrammaticallywhile grouping by ResName.
然后在按ResName分组的同时以编程方式连接AppName。
回答by OMG Ponies
MySQL
MySQL
SELECT r.name,
GROUP_CONCAT(a.name SEPARATOR ',')
FROM RESOURCES r
JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id
JOIN APPLICATIONS a ON a.id = ar.app_id
GROUP BY r.name
SQL Server (2005+)
SQL Server (2005+)
SELECT r.name,
STUFF((SELECT ','+ a.name
FROM APPLICATIONS a
JOIN APPLICATIONRESOURCES ar ON ar.app_id = a.id
WHERE ar.resource_id = r.id
GROUP BY a.name
FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
FROM RESOURCES r
SQL Server (2017+)
SQL Server (2017+)
SELECT r.name,
STRING_AGG(a.name, ',')
FROM RESOURCES r
JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id
JOIN APPLICATIONS a ON a.id = ar.app_id
GROUP BY r.name
Oracle
甲骨文
I recommend reading about string aggregation/concatentation in Oracle.
回答by Robert Harvey
Using COALESCE to Build Comma-Delimited String in SQL Server
http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string
使用 COALESCE 在 SQL Server 中构建逗号分隔的字符串
http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string
Example:
例子：
DECLARE @EmployeeList varchar(100)
SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') +
CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1
SELECT @EmployeeList
回答by marc_s
I don't know if there's any solution to do this in a database-agnostic way, since you most likely will need some form of string manipulation, and those are typically different between vendors.
我不知道是否有任何解决方案可以以与数据库无关的方式执行此操作，因为您很可能需要某种形式的字符串操作，而这些操作通常因供应商而异。
For SQL Server 2005 and up, you could use:
对于 SQL Server 2005 及更高版本，您可以使用：
SELECT
r.ID, r.Name,
Resources = STUFF(
(SELECT ','+a.Name
FROM dbo.Applications a
INNER JOIN dbo.ApplicationsResources ar ON ar.app_id = a.id
WHERE ar.resource_id = r.id
FOR XML PATH('')), 1, 1, '')
FROM
dbo.Resources r
It uses the SQL Server 2005 FOR XML PATHconstruct to list the subitems (the applications for a given resource) as a comma-separated list.
它使用 SQL Server 2005FOR XML PATH构造以逗号分隔列表的形式列出子项（给定资源的应用程序）。
Marc
马克
回答by Jamie Wong
I believe what you want is:
我相信你想要的是：
SELECT ItemName, GROUP_CONCAT(DepartmentId) FROM table_name GROUP BY ItemName
SELECT ItemName, GROUP_CONCAT(DepartmentId) FROM table_name GROUP BY ItemName
If you're using MySQL
如果您使用的是 MySQL
Reference
参考
回答by Kenneth
Assuming SQL Server:
假设 SQL Server：
Table structure:
表结构：
CREATE TABLE [dbo].[item_dept](
[ItemName] char(20) NULL,
[DepartmentID] int NULL
)
Query:
询问：
SELECT ItemName,
STUFF((SELECT ',' + rtrim(convert(char(10),DepartmentID))
FROM item_dept b
WHERE a.ItemName = b.ItemName
FOR XML PATH('')),1,1,'') DepartmentID
FROM item_dept a
GROUP BY ItemName
Results:
结果：
ItemName DepartmentID
item1 21,13,9,36
item2 4,9,44
回答by Martin Smith
From next version of SQL Serveryou will be able to do
从下一个版本的 SQL Server你将能够做到
SELECT r.name,
STRING_AGG(a.name, ',')
FROM RESOURCES r
JOIN APPLICATIONSRESOURCES ar
ON ar.resource_id = r.id
JOIN APPLICATIONS a
ON a.id = ar.app_id
GROUP BY r.name
For previous versions of the product there are quite a wide variety of different approaches to this problem. An excellent review of them is in the article: Concatenating Row Values in Transact-SQL.
对于该产品的先前版本，有多种不同的方法可以解决此问题。对它们的出色评论在文章中：在 Transact-SQL 中连接行值。
Concatenating values when the number of items are not known
- Recursive CTE method
- The blackbox XML methods
- Using Common Language Runtime
- Scalar UDF with recursion
- Table valued UDF with a WHILE loop
- Dynamic SQL
- The Cursor approach
.
Non-reliable approaches
- Scalar UDF with t-SQL update extension
- Scalar UDF with variable concatenation in SELECT
当项目数未知时连接值
- 递归 CTE 方法
- 黑盒 XML 方法
- 使用公共语言运行时
- 带递归的标量 UDF
- 带有 WHILE 循环的表值 UDF
- 动态 SQL
- 光标方法
。
不可靠的方法
- 带有 t-SQL 更新扩展的标量 UDF
- SELECT 中带有变量连接的标量 UDF
回答by user1696097
I think we could write in the following way to retrieve(below code is just an example, please modify as needed):
我想我们可以用下面的方式来检索（以下代码只是一个例子，请根据需要修改）：
Create FUNCTION dbo.ufnGetEmployeeMultiple(@DepartmentID int)
RETURNS VARCHAR(1000) AS
BEGIN
DECLARE @Employeelist varchar(1000)
SELECT @Employeelist = COALESCE(@Employeelist + ', ', '') + E.LoginID
FROM humanresources.Employee E
Left JOIN humanresources.EmployeeDepartmentHistory H ON
E.BusinessEntityID = H.BusinessEntityID
INNER JOIN HumanResources.Department D ON
H.DepartmentID = D.DepartmentID
Where H.DepartmentID = @DepartmentID
Return @Employeelist
END
SELECT D.name as Department, dbo.ufnGetEmployeeMultiple (D.DepartmentID)as Employees
FROM HumanResources.Department D
SELECT Distinct (D.name) as Department, dbo.ufnGetEmployeeMultiple (D.DepartmentID) as
Employees
FROM HumanResources.Department D
回答by Pavel Zimogorov
MySQL
MySQL
SELECT r.name,
GROUP_CONCAT(a.name SEPARATOR ',')
FROM RESOURCES r
JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id
JOIN APPLICATIONS a ON a.id = ar.app_id
GROUP BY r.name
**
**
MS SQL Server
微软 SQL 服务器
SELECT r.name,
STUFF((SELECT ','+ a.name
FROM APPLICATIONS a
JOIN APPLICATIONRESOURCES ar ON ar.app_id = a.id
WHERE ar.resource_id = r.id
GROUP BY a.name
FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
FROM RESOURCES r
GROUP BY deptno;
Oracle
甲骨文
SELECT r.name,
LISTAGG(a.name SEPARATOR ',') WITHIN GROUP (ORDER BY a.name)
FROM RESOURCES r
JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id
JOIN APPLICATIONS a ON a.id = ar.app_id
GROUP BY r.name;
回答by Don
To be agnostic, drop back and punt.
要成为不可知论者，请退后一步。
Select a.name as a_name, r.name as r_name
from ApplicationsResource ar, Applications a, Resources r
where a.id = ar.app_id
and r.id = ar.resource_id
order by r.name, a.name;
Now user your server programming language to concatenate a_names while r_name is the same as the last time.
现在使用您的服务器编程语言连接 a_names 而 r_name 与上次相同。
回答by Abe Miessler
This will do it in SQL Server:
这将在 SQL Server 中完成：
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Convert(nvarchar(8),DepartmentId)
FROM Table
SELECT @listStr
