How to convert a recordset to a delimited string in SQL Server

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

1
2
3

into '1,2,3' 开发者_如何学Gostring in SQL Server?

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

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

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

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