学步园

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