DECLARE @TempTable table(UserID int , UserName nvarchar(50)); insert into @TempTable (UserID,UserName) values (1,'a') insert into @TempTable (UserID,UserName) values (2,'b') select UserID,UserName from @TempTable FOR XML PATH DECLARE @TempTable table(UserID int , UserName nvarchar(50)); insert into @TempTable (UserID,UserName) values (1,'a') insert into @TempTable (UserID,UserName) values (2,'b') select UserID,UserName from @TempTable FOR XML PATH('logic') DECLARE @TempTable table(UserID int , UserName nvarchar(50)); insert into @TempTable (UserID,UserName) values (1,'a') insert into @TempTable (UserID,UserName) values (2,'b') select UserID,UserName from @TempTable FOR XML PATH('') DECLARE @TempTable table(UserID int , UserName nvarchar(50)); insert into @TempTable (UserID,UserName) values (1,'a') insert into @TempTable (UserID,UserName) values (2,'b') select CAST(UserID AS varchar) + '',UserName + '' from @TempTable FOR XML PATH('') DECLARE @TempTable table(UserID int , UserName nvarchar(50)); insert into @TempTable (UserID,UserName) values (1,'a') insert into @TempTable (UserID,UserName) values (2,'b') select CAST(UserID AS varchar) + ',',UserName + '',';' from @TempTable FOR XML PATH('') DECLARE @TempTable table(UserID int , UserName nvarchar(50)); insert into @TempTable (UserID,UserName) values (1,'a') insert into @TempTable (UserID,UserName) values (2,'b') select '{' + CAST(UserID AS varchar) + ',','"' +UserName + '"','}' from @TempTable FOR XML PATH('') DECLARE @T1 table(UserID int , UserName nvarchar(50),CityName nvarchar(50)); insert into @T1 (UserID,UserName,CityName) values (1,'a','SHANGHAI') insert into @T1 (UserID,UserName,CityName) values (2,'b','BEIJING') insert into @T1 (UserID,UserName,CityName) values (3,'c','SHANGHAI') insert into @T1 (UserID,UserName,CityName) values (4,'d','BEIJING') insert into @T1 (UserID,UserName,CityName) values (5,'e','SHANGHAI') SELECT B.CityName,LEFT(UserList,LEN(UserList)-1) as '用户名' FROM ( SELECT CityName, ( SELECT UserName+',' FROM @T1 WHERE CityName=A.CityName FOR XML PATH('') ) AS UserList FROM @T1 A GROUP BY CityName ) B
http://blog.csdn.net/fireliangbin/article/details/7783611