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 ,        UserNameFROM    @TempTableFOR     XML PATH--其实PATH() 括号内的参数是控制节点名称的SELECT  UserID ,        UserNameFROM    @TempTableFOR     XML PATH(‘lzy‘)--这样就不显示上级节点了SELECT  UserID ,        UserNameFROM    @TempTableFOR     XML PATH(‘‘)--大家可以根据自己需要的格式进行组合SELECT  CAST(UserID AS VARCHAR) + ‘‘ ,        UserName + ‘‘FROM    @TempTableFOR     XML PATH(‘‘)SELECT  CAST(UserID AS VARCHAR) + ‘,‘ ,        UserName + ‘‘ ,        ‘;‘FROM    @TempTableFOR     XML PATH(‘‘)SELECT  ‘{‘ + CAST(UserID AS VARCHAR) + ‘,‘ ,        ‘"‘ + UserName + ‘"‘ ,        ‘}‘FROM    @TempTableFOR     XML PATH(‘‘)

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    @T1SELECT  CityName ,        ( SELECT    UserName + ‘,‘          FROM      @T1          WHERE     CityName = A.CityName        FOR          XML PATH(‘‘)        ) AS UserListFROM    @T1 AGROUP 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