【T-SQL系列】FOR XML PATH 語句的應用

標籤：style   blog   http   ar   color   os   sp   for   on   

原文:【T-SQL系列】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 ,        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

對應結果集：

【T-SQL系列】FOR XML PATH 語句的應用