sql xml xquery (1)

来源：互联网发布：linux gdb调试 call 编辑：程序博客网时间：2024/05/03 19:30
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 PATHDECLARE @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