sql里将重复行数据合并为一行数据使用逗号进行分隔（sql查询出的结果重复的只显示一条）越早知道越好

select * from @T1

—–最优的方式

SELECT CityName,STUFF((SELECT ‘,’ + UserName FROM @T1 subTitle WHERE CityName=A.CityName FOR XML PATH(”)),1, 1, ”) AS A

FROM @T1 A

GROUP BY CityName

—-第二种方式

SELECT B.CityName,LEFT(UserList,LEN(UserList)-1)

FROM (

SELECT CityName,(SELECT UserName+’,’ FROM @T1 WHERE CityName=A.CityName FOR XML PATH(”)) AS UserList

FROM @T1 A

GROUP BY CityName

) B

stuff(select ‘,’ + fieldname from tablename for xml path(”)),1,1,”)