Sql Server 合并行

 

Sql 合并行

如下：

ID	Name
1	Tom
1	John
2	Carrier
2	Wendy
1	Rick

要求顯示下列結果

ID	Name
1	Tom,John,Rick
2	Carrier,Wendy

Sql:

select id,

（SELECT STUFF(b.v.value('/r[1]','varchar(100)'),1,1,'')

FROM

(SELECT v=(

SELECT ',' + Name

FROM TableName

where id=c.id

FOR XML PATH(''),ROOT('r'),TYPE

)) b） as Name

from TableName c

group by id