如何在SQL Server中将多行文本合并为单个文本字符串?

本文介绍了如何在SQL Server中将多行文本合并为单个文本字符串?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

考虑一个包含名称的数据库表，该表具有三行:

Consider a database table holding names, with three rows:

Peter Paul Mary

是否有一种简单的方法可以将其转换为单个字符串Peter, Paul, Mary?

Is there an easy way to turn this into a single string of Peter, Paul, Mary?

推荐答案

如果您使用的是SQL Server 2017或Azure，请参见 Mathieu Renda答案.

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

当我试图将两个具有一对多关系的表联接在一起时，我遇到了类似的问题.在SQL 2005中，我发现XML PATH方法可以非常轻松地处理行的串联.

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 PATH method can handle the concatenation of the rows very easily.

如果有一个名为STUDENTS

SubjectID StudentName ---------- ------------- 1 Mary 1 John 1 Sam 2 Alaina 2 Edward

我期望的结果是:

SubjectID StudentName ---------- ------------- 1 Mary, John, Sam 2 Alaina, Edward

我使用了以下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]

如果可以在开头合并逗号并使用substring跳过第一个逗号，则可以以更紧凑的方式执行相同的操作，因此无需执行子查询:

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

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