考虑一个包含名称的数据库表,其中包含三行:
Peter Paul Mary
有没有一种简单的方法可以把它变成一个字符串Peter, Paul, Mary?
Peter, Paul, Mary
当我尝试加入两个具有一对多关系的表时,我遇到了类似的问题。在 SQL 2005 中,我发现该XML PATH方法可以非常轻松地处理行的连接。
XML PATH
如果有一个表叫STUDENTS
STUDENTS
SubjectID StudentName ---------- ------------- 1 Mary 1 John 1 Sam 2 Alaina 2 Edward
我预期的结果是:
SubjectID StudentName ---------- ------------- 1 Mary, John, Sam 2 Alaina, Edward
我使用了以下内容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 (''), TYPE ).value('text()[1]','nvarchar(max)') [Students] FROM dbo.Students ST2 ) [Main]
如果您可以在开头连接逗号并使用substring跳过第一个逗号,那么您可以以更紧凑的方式执行相同的操作,这样您就不需要执行子查询:
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 (''), TYPE ).value('text()[1]','nvarchar(max)'), 2, 1000) [Students] FROM dbo.Students ST2
从 SQL Server 的下一个版本开始,我们终于可以跨行连接,而无需求助于任何变量或 XML 巫术。
STRING_AGG (Transact-SQL)
不分组
SELECT STRING_AGG(Name, ', ') AS Departments FROM HumanResources.Department;
分组:
SELECT GroupName, STRING_AGG(Name, ', ') AS Departments FROM HumanResources.Department GROUP BY GroupName;
具有分组和子排序
SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments FROM HumanResources.Department GROUP BY GroupName;