How to convert a recordset to a delimited string i

问题:

Is there a way to convert a single column record set such as

1
2
3

into '1,2,3' string in SQL Server?

回答1:

I've had success creating a function using Coalesce like below to do this. You can then use the function in your select statement to get the delimited string. (You can add parameters to your function and use them in the coalesce select statement as needed.)

CREATE FUNCTION [dbo].[fn_MyFunction]
(
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

    DECLARE @str NVARCHAR(MAX)

    DECLARE @Delimiter CHAR(2) 
    SET @Delimiter = ', '

    SELECT @str = COALESCE(@str + @Delimiter,'') + AColumn
    FROM dbo.myTable

    RETURN RTRIM(LTRIM(@str))

END

回答2:

COALESCE is decent way to do this

http://msdn.microsoft.com/en-us/library/ms190349.aspx

DECLARE @Out VARCHAR(2048)
Select @Out = COALESCE(@Out + ', ', '') + [YourColumn] from YourTable

print @Out

回答3:

select stuff( (select ',' + YourColumn
               from YourTable
               for xml path('')), 1, 1, '')

Note: The stuff function just removes the first comma from the result string.