一.定义表变量
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 * 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,'')
效果图: