SQL2005下利用XML进行项目的合并与拆分

SQL2005 下利用 XML 进行项目的合并与拆分 <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

作者： HappyFlyStone

转载请注明出处

　　通常情况下我们对相同数值项目进行分组求和，那是相当的简单啦，只要 select .. group by 加聚合函数就行了，可是对于串一类的项目进行合并时就不这么简单了，同样分解一个按指定分隔符分隔的串或分析指定位置的串，在下我们通常是创建一个函数，然后分组或提取就行了，现我们主要讨论在下利用 xml 来完成这个工作。

1 、先来一个简单点，如下的例子对 aaa 相同的项目合并。

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

-- Author: HappyFlyStone

-- Date : 2009-01-04 20:57:59

-- Ver: 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 NT5.0(Build 2195: Service Pack 4)

--

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

-- Test Data: tb

IF OBJECT_ID ( 'tb' ) IS NOT NULL

DROP TABLE tb

Go

CREATE TABLE tb ( aaa INT , bbb INT )

Go

INSERT INTO tb

SELECT 1 , 2 UNION ALL

SELECT 1 , 3 UNION ALL

SELECT 1 , 4 UNION ALL

SELECT 2 , 2 UNION ALL

SELECT 2 , 5

GO

-- 查询 1

select

aaa ,

[values] = stuff ( replace ( replace (( select [bbb]

from tb

where aaa = t . aaa for xml AUTO ),

'"/><tb bbb="' , ',' ),

'"/>' , '' ), 1 , 9 , '' )

from tb t

group by aaa

-- 查询 2

SELECT *

FROM (

SELECT DISTINCT

aaa

FROM tb

) A

OUTER APPLY(

SELECT

[bbb] = STUFF ( REPLACE ( REPLACE (

(

SELECT [bbb] FROM tb N

WHERE aaa = A . aaa

FOR XML AUTO

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

) N

-- 查询 3

select

aaa ,

[values] = stuff (( select ',' + ltrim ( [bbb] )

from tb t

where aaa = tb . aaa for xml path ( '' )), 1 , 1 , '' )

from tb

group by aaa

drop table tb

-- 查询结果

/*

aaa values

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

1 2,3,4

2 2,5

(2 行受影响 )

*/

2 、来个两个表关联操作并实现行列转换的

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

-- Author: HappyFlyStone

-- Date : 2009-01-04 20:57:59

-- Ver: 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 NT5.0(Build 2195: Service Pack 4)

--

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

-- Test Data: ta

IF OBJECT_ID ( 'ta' ) IS NOT NULL

DROP TABLE ta

Go

CREATE TABLE ta ( pid INT , tid INT , name NVARCHAR ( 6 ))

Go

INSERT INTO ta

SELECT 1 , 1 , 'hy3500' UNION ALL

SELECT 1 , 2 , 'aabbcc' UNION ALL

SELECT 2 , 3 , '1111' UNION ALL

SELECT 2 , 4 , '2222'

GO

-- Test Data: tb

IF OBJECT_ID ( 'tb' ) IS NOT NULL

DROP TABLE tb

Go

CREATE TABLE tb ( NAME NVARCHAR ( 2 ), id INT )

Go

INSERT INTO tb

SELECT ' 型号 ' , 1 UNION ALL

SELECT ' 参数 ' , 2

GO

--Start

SELECT t . [name] , A , B

FROM (

SELECT

B . [NAME] ,

CAST (( SELECT [name] FROM TA WHERE PID = A . PID FOR XML PATH ( '' )) AS XML ) AS X

FROM TA A

border-right: medium none; padding-right: 0cm; border-top: medium none; padding-left: 0cm; padding-bottom: 0cm; margin: 0cm 0cm 0pt; border-left: medium none; padding-top: 0cm; border-bottom: medium n