如何将多行中的文本连接成 SQL Server 中的单个文本字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/194852/
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 to concatenate text from multiple rows into a single text string in SQL server?
提问by JohnnyM
Consider a database table holding names, with three rows:
考虑一个包含名称的数据库表，包含三行：
Peter
Paul
Mary
Is there an easy way to turn this into a single string of Peter, Paul, Mary?
有没有一种简单的方法可以将其转换为单个字符串Peter, Paul, Mary？
采纳答案by JohnnyM
If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.
如果您使用的是 SQL Server 2017 或 Azure，请参阅Mathieu Renda 的回答。
I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATHmethod can handle the concatenation of the rows very easily.
当我尝试连接两个具有一对多关系的表时，我遇到了类似的问题。在 SQL 2005 中，我发现该XML PATH方法可以非常轻松地处理行的连接。
If there is a table called STUDENTS
如果有一个表叫 STUDENTS
SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
Result I expected was:
我预期的结果是：
SubjectID StudentName
---------- -------------
1 Mary, John, Sam
2 Alaina, Edward
I used the following T-SQL:
我使用了以下内容T-SQL：
SELECT Main.SubjectID,
LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
(
SELECT DISTINCT ST2.SubjectID,
(
SELECT ST1.StudentName + ',' AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH ('')
) [Students]
FROM dbo.Students ST2
) [Main]
You can do the same thing in a more compact way if you can concat the commas at the beginning and use substringto skip the first one so you don't need to do a sub-query:
如果您可以在开头连接逗号并使用substring跳过第一个逗号，则可以以更紧凑的方式执行相同的操作，因此您无需执行子查询：
SELECT DISTINCT ST2.SubjectID,
SUBSTRING(
(
SELECT ','+ST1.StudentName AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH ('')
), 2, 1000) [Students]
FROM dbo.Students ST2
回答by Chris Shaffer
This answer may return unexpected resultsFor consistent results, use one of the FOR XML PATH methods detailed in other answers.
此答案可能会返回意外结果要获得一致的结果，请使用其他答案中详述的 FOR XML PATH 方法之一。
Use COALESCE:
使用COALESCE：
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
Just some explanation (since this answer seems to get relatively regular views):
只是一些解释（因为这个答案似乎获得了相对常规的观点）：
- Coalesce is really just a helpful cheat that accomplishes two things:
- Coalesce 实际上只是一个有用的作弊工具，可以完成两件事：
1) No need to initialize @Nameswith an empty string value.
1) 不需要@Names用空字符串值初始化。
2) No need to strip off an extra separator at the end.
2）不需要在最后剥离额外的分隔符。
- The solution above will give incorrect results if a row has a NULLName value (if there is a NULL, the NULLwill make
@NamesNULLafter that row, and the next row will start over as an empty string again. Easily fixed with one of two solutions:
- 如果一行有一个NULLName 值，上面的解决方案将给出不正确的结果（如果有一个NULL，则该行之后的NULL将变为
@NamesNULL，下一行将再次作为空字符串重新开始。使用两个中的一个轻松修复解决方案：
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL
or:
或者：
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') +
ISNULL(Name, 'N/A')
FROM People
Depending on what behavior you want (the first option just filters NULLs out, the second option keeps them in the list with a marker message [replace 'N/A' with whatever is appropriate for you]).
根据您想要的行为（第一个选项只是过滤掉NULL，第二个选项将它们保留在带有标记消息的列表中 [用适合您的任何内容替换 'N/A']）。
回答by Mathieu Renda
SQL Server 2017+ and SQL Azure: STRING_AGG
SQL Server 2017+ 和 SQL Azure：STRING_AGG
Starting with the next version of SQL Server, we can finally concatenate across rows without having to resort to any variable or XML witchery.
从 SQL Server 的下一个版本开始，我们最终可以跨行连接，而不必求助于任何变量或 XML 巫术。
Without grouping
不分组
SELECT STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department;
With grouping :
分组：
SELECT GroupName, STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department
GROUP BY GroupName;
With grouping and sub-sorting
带有分组和子排序
SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
FROM HumanResources.Department
GROUP BY GroupName;
回答by jens frandsen
One method not yet shown via the XMLdata()command in MS SQL Server is:
尚未通过XMLdata()MS SQL Server 中的命令显示的一种方法是：
Assume table called NameList with one column called FName,
假设名为 NameList 的表有一个名为 FName 的列，
SELECT FName + ', ' AS 'data()'
FROM NameList
FOR XML PATH('')
returns:
返回：
"Peter, Paul, Mary, "
Only the extra comma must be dealt with.
只需要处理多余的逗号。
Edit:As adopted from @NReilingh's comment, you can use the following method to remove the trailing comma. Assuming the same table and column names:
编辑：从@NReilingh 的评论中采用，您可以使用以下方法删除尾随逗号。假设表名和列名相同：
STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(FName)) AS 'data()' FROM NameList
FOR XML PATH('')),' #!',', '), 1, 2, '') as Brands
回答by Steven Chong
In SQL Server 2005
在SQL Server 2005 中
SELECT Stuff(
(SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N'')
In SQL Server 2016
在 SQL Server 2016 中
you can use the FOR JSON syntax
您可以使用FOR JSON 语法
i.e.
IE
SELECT per.ID,
Emails = JSON_VALUE(
REPLACE(
(SELECT _ = em.Email FROM Email em WHERE em.Person = per.ID FOR JSON PATH)
,'"},{"_":"',', '),'$[0]._'
)
FROM Person per
And the result will become
结果会变成
Id Emails
1 [email protected]
2 NULL
3 [email protected], [email protected]
This will work even your data contains invalid XML characters
即使您的数据包含无效的 XML 字符，这也会起作用
the '"},{"_":"'is safe because if you data contain '"},{"_":"',it will be escaped to "},{\"_\":\"
这'"},{"_":"'是安全的，因为如果您的数据包含'"},{"_":"',它将被转义到"},{\"_\":\"
You can replace ', 'with any string separator
您可以替换', '为任何字符串分隔符
And in SQL Server 2017, Azure SQL Database
在 SQL Server 2017 中，Azure SQL 数据库
You can use the new STRING_AGG function
您可以使用新的STRING_AGG 函数
回答by Darryl Hein
In MySQL there is a function, GROUP_CONCAT(), which allows you to concatenate the values from multiple rows. Example:
在 MySQL 中有一个函数GROUP_CONCAT()，它允许您连接多行中的值。例子：
SELECT 1 AS a, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS people
FROM users
WHERE id IN (1,2,3)
GROUP BY a
回答by pedram
Use COALESCE- Learn more from here
使用COALESCE-从这里了解更多信息
For an example:
例如：
102
103
104
102
103
104
Then write below code in sql server,
然后在sql server中写下面的代码，
Declare @Numbers AS Nvarchar(MAX) -- It must not be MAX if you have few numbers
SELECT @Numbers = COALESCE(@Numbers + ',', '') + Number
FROM TableName where Number IS NOT NULL
SELECT @Numbers
Output would be:
输出将是：
102,103,104
回答by hgmnz
Postgres arrays are awesome. Example:
Postgres 数组很棒。例子：
Create some test data:
创建一些测试数据：
postgres=# \c test
You are now connected to database "test" as user "hgimenez".
test=# create table names (name text);
CREATE TABLE
test=# insert into names (name) values ('Peter'), ('Paul'), ('Mary');
INSERT 0 3
test=# select * from names;
name
-------
Peter
Paul
Mary
(3 rows)
Aggregate them in an array:
将它们聚合在一个数组中：
test=# select array_agg(name) from names;
array_agg
-------------------
{Peter,Paul,Mary}
(1 row)
Convert the array to a comma delimited string:
将数组转换为逗号分隔的字符串：
test=# select array_to_string(array_agg(name), ', ') from names;
array_to_string
-------------------
Peter, Paul, Mary
(1 row)
DONE
完毕
Since PostgreSQL 9.0 it is even easier.
从 PostgreSQL 9.0 开始就更容易了。
回答by Alex
Oracle 11g Release 2 supports the LISTAGG function. Documentation here.
Oracle 11g 第 2 版支持 LISTAGG 功能。文档在这里。
COLUMN employees FORMAT A50
SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
3 rows selected.
Warning
警告
Be careful implementing this function if there is possibility of the resulting string going over 4000 characters. It will throw an exception. If that's the case then you need to either handle the exception or roll your own function that prevents the joined string from going over 4000 characters.
如果结果字符串可能超过 4000 个字符，请小心执行此函数。它会抛出异常。如果是这种情况，那么您需要处理异常或滚动您自己的函数，以防止连接的字符串超过 4000 个字符。
回答by Yogesh Bhadauirya
In SQL Server 2005 and later, use the query below to concatenate the rows.
在 SQL Server 2005 及更高版本中，使用下面的查询连接行。
DECLARE @t table
(
Id int,
Name varchar(10)
)
INSERT INTO @t
SELECT 1,'a' UNION ALL
SELECT 1,'b' UNION ALL
SELECT 2,'c' UNION ALL
SELECT 2,'d'
SELECT ID,
stuff(
(
SELECT ','+ [Name] FROM @t WHERE Id = t.Id FOR XML PATH('')
),1,1,'')
FROM (SELECT DISTINCT ID FROM @t ) t
