如何在SQL Server 2008中将多个行转换为一个字符串?(复制)

This question already has an answer here:

这个问题已经有了答案:

Concatenating Column Values into a Comma-Separated List 8 answers
将列值连接到逗号分隔的列表8答案中

How to concat many rows into one strings?

如何把许多行合并成一串?

Query:

查询:

SELECT name FROM mytable;

Result:

结果:

name
----
kim
lee
park
cho

Just I want.

只是我想要的。

name
----
kim,lee,park,cho

Impossible?

不可能吗?

3 个解决方案

#1

Try this one -

试试这个,

DECLARE @temp TABLE (name NVARCHAR(50))

INSERT INTO @temp (name)
VALUES ('kim'),('lee'),('park'),('cho')

SELECT STUFF((
    SELECT ',' + name
    FROM @temp
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

Output -

输出-

kim,lee,park,cho

#2

I have been working on something similar this morning, adding the case will also work for 1 returned row. Hope this helps. Else follow the link the first comment sent you.

我今天上午一直在做类似的工作，添加案例也适用于返回行。希望这个有帮助。否则遵循第一个评论发送给你的链接。

Declare @NameList VarChar(MAX) = ''    
select @NameList = 
    case when @NameList = ''
        then coalesce(Name, '')
        else @NameList + coalesce(',' + Name, '')
    end
from mytable
print @NameList

Ben

本

#3

Try this

试试这个

 SELECT   
name= substring((SELECT ( ', ' + Name)
                       FROM TableName t
                       FOR XML PATH( '' )
                      ), 3, 1000 )  FROM mytable tn

#1