SqlServer 列转换为行，字段字符串相连

DECLARE @T1 table(UserID int , UserName nvarchar(50),CityName nvarchar(50));  

insert into @T1 (UserID,UserName,CityName) values (1,'a','上海')  

insert into @T1 (UserID,UserName,CityName) values (2,'b','北京')  

insert into @T1 (UserID,UserName,CityName) values (3,'c','上海')  

insert into @T1 (UserID,UserName,CityName) values (4,'d','北京')  

insert into @T1 (UserID,UserName,CityName) values (5,'e','上海') 

--select a.UserName from @T1 a
select a.UserName from @T1 a for xml path('row')



--select cityname,(select a.UserName from @T1 a where a.CityName=t.CityName for xml path('row')) from @T1 t group by CityName

--select cityname,replace
--((select a.UserName from @T1 a where a.CityName=t.CityName for xml path('row'))
--,'<row><UserName>','')
-- from @T1 t group by CityName

--第一种方法：
select cityname,
REPLACE(
replace
((select a.UserName from @T1 a where a.CityName=t.CityName for xml path('row'))
,'<row><UserName>','')
,'</UserName></row>',',')
 from @T1 t 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