1 一.定义表变量 2  3 DECLARE @T1 table 4 ( 5 UserID int ,  6 UserName nvarchar(50), 7 CityName nvarchar(50) 8 ); 9 10 insert into @T1 (UserID,UserName,CityName) values (1,‘a‘,‘上海‘)11 insert into @T1 (UserID,UserName,CityName) values (2,‘b‘,‘北京‘)12 insert into @T1 (UserID,UserName,CityName) values (3,‘c‘,‘上海‘)13 insert into @T1 (UserID,UserName,CityName) values (4,‘d‘,‘北京‘)14 insert into @T1 (UserID,UserName,CityName) values (5,‘e‘,‘上海‘)15 16 select * from @T117 18 -----最优的方式19 SELECT CityName,STUFF((SELECT ‘,‘ + UserName FROM @T1 subTitle WHERE CityName=A.CityName FOR XML PATH(‘‘)),1, 1, ‘‘) AS A20 FROM @T1 A21 GROUP BY CityName22 23 ----第二种方式24 SELECT B.CityName,LEFT(UserList,LEN(UserList)-1) 25 FROM (26   SELECT CityName,(SELECT UserName+‘,‘ FROM @T1 WHERE CityName=A.CityName FOR XML PATH(‘‘)) AS UserList27   FROM @T1 A 28   GROUP BY CityName29      ) B30 31 stuff(select ‘,‘ + fieldname  from tablename for xml path(‘‘)),1,1,‘‘)32 这一整句的作用是将多行fieldname字段的内容串联起来，用逗号分隔。33 34 for xml path是SQL Server 2005以后版本支持的一种生成XML的方式。35 36 stuff函数的作用是去掉字符串最前面的逗号分隔符。