从结果集创建长字符串 [英] Creating a long string from a result set

问题描述

我在存储过程中的 MS-SQL 中有一个结果集，假设它有一个 VARCHAR 列，但有很多行.我想创建一个包含所有这些值的逗号分隔字符串，是否有一种简单的方法可以做到这一点，或者我是否必须逐个遍历每个结果并手动构建字符串?

I have a result set in MS-SQL within a stored procedure, and lets say it has one VARCHAR column, but many rows. I want to create a comma separated string conataining all these values, Is there an easy way of doing this, or am I going to have to step through each result and build the string up manually?

我最好在存储过程本身中执行此操作.

Preferably I'd like to do this in the Stored Procedure itself.

推荐答案

这是一种方法(使用 AdventureWorks2008 DB):

Here is one way (using AdventureWorks2008 DB):

DECLARE @name varchar(255)
SET @name = NULL

select @Name = COALESCE(@Name + ',','') + LastName from Person.Person
Select @name

这是另一个(对于 SQL 2005 以后):

And here is another (for SQL 2005 onwards):

SELECT 
    LastName + ','
FROM 
    Person.Person
FOR XML PATH('') 

在这两种情况下，您都需要删除结尾的逗号,"(可以使用 STUFF() 函数)

In both cases you will need to remove the trailing comma ',' (can use STUFF() function)

这篇关于从结果集创建长字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！