利用xml改造以前在2000下的合并相同项目的函数

------------------------------------

-- Author:  happyflystone 

-- Date:2008-11-23 22:17:36

-- Description:利用xml改造以前在2000下的合并相同项目的函数

------------------------------------

 

--  运行环境：

 

SELECT @@version

/*

-------------------------------------------------

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)

    Apr 14 2006 01:12:25

    Copyright (c) 1988-2005 Microsoft Corporation

    Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

(1 行受影响)

*/

-- Test Data: TA

IF OBJECT_ID('TA') IS NOT NULL

    DROP TABLE TA

GO

CREATE TABLE TA(ID INT,DATA VARCHAR(50))

INSERT TA VALUES(1,'aa')

INSERT TA VALUES(1,'bb')

INSERT TA VALUES(1,'cc')

INSERT TA VALUES(2,'eee')

INSERT TA VALUES(2,'fff')

 

GO

 

--　先来看看一个合并的例子：

 

CREATE FUNCTION F_STR(@ID INT)

RETURNS VARCHAR(100)

AS

BEGIN

    DECLARE @S VARCHAR(100)

    SELECT @S = ISNULL(@S+',','')

                   +DATA

     FROM TA

     WHERE ID = @ID

    RETURN @S

END

GO

SELECT ID,DBO.F_STR(ID) AS COL

FROM TA

GROUP BY ID

/*

ID             COL

----------- ----------------------------

1           aa,bb,cc

2           eee,fff

 

*/

DROP FUNCTION F_STR

GO

 

 

-- 下面我们利用XML来合并相同项，我们创建一个合并同类项的函数。

 

CREATE FUNCTION F_STR(@ID int)

RETURNS VARCHAR(100)

AS

BEGIN

     DECLARE @x XML,@ret VARCHAR(100)

     SET @x=

          (SELECT DATA

          FROM TA

          WHERE ID = @ID

              FOR XML PATH(''),ROOT('v'));

     SELECT @ret = CAST(

                            @x.query('

                                 <v>

                                  { for $i in //DATA

                                     return concat(string($i),",")

                                  }

                                 </v>').value('/v[1]','VARCHAR(100)')

                            AS VARCHAR(100))

    RETURN @ret

END

GO

 

-- 运行及结果，并删除函数

 

SELECT ID,DBO.F_STR(ID) AS COL

FROM TA

GROUP BY ID

/*

ID             COL

----------- ----------------------------

1           aa,bb,cc

2           eee,fff

 

*/

DROP FUNCTION F_STR

 

 

 

-- 最后我贴上经常使用XML语法合并同类项一般SQL写法：

-- 1 (AUTO):

 

SELECT

     ID,

     [VALUES]=STUFF(REPLACE(REPLACE((SELECT DATA

                                          FROM TA

                                          WHERE ID=T.ID FOR XML AUTO),

                                          '"/><TA DATA="',','),

                                 '"/>',''),1,10,'')

FROM TA T

GROUP BY ID

 

-- 2:(OUTER APPLY,AUTO)

 

SELECT *

FROM(

    SELECT DISTINCT

        id

    FROM ta

)A

OUTER APPLY(

    SELECT

        [bbb]= STUFF(REPLACE(REPLACE(

            (

                SELECT data FROM ta N

                WHERE id = A.id

                FOR XML AUTO

            ), '<N data="', ','), '"/>', ''), 1, 1, '')

)N

 

-- 3:(PATH)

 

SELECT

     ID,

     [VALUES]=STUFF((SELECT ','+LTRIM(DATA)

                       FROM TA T

                       WHERE ID=TA.ID FOR XML PATH('')), 1, 1, '')

FROM TA

GROUP BY ID

 

 

-- 上面的SQL运行结果都是一样，如下：

 

/*

ID         

----------- ----------------------------

1           aa,bb,cc

2           eee,fff

 

*/