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