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

------------------------------------<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

-- 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

*/