开发者

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

开发者 https://www.devze.com 2023-02-04 20:19 出处:网络
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

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号