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

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

作者：胡林

时间：2009年1月4日

 

转载请注明出处

通常情况下我们对相同数值项目进行分组求和，那是相当的简单啦，只要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: tbIF OBJECT_ID('tb') IS NOT NULL  DROP TABLE tbGoCREATE TABLE tb(aaa INT,bbb INT)GoINSERT INTO tbSELECT 1,2 UNION ALLSELECT 1,3 UNION ALLSELECT 1,4 UNION ALLSELECT 2,2 UNION ALLSELECT 2,5 GO --查询1select  aaa,[values]=stuff(replace(replace((select [bbb]  from tb  where aaa=t.aaa for xml AUTO), '"/><tb bbb="',','), '"/>',''),1,9,'')from tb tgroup by aaa  --查询2SELECT * 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  --查询3select 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,42 2,5(2 行受影响)*/

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

-------------------------------------------------------------------------- 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: taIF OBJECT_ID('ta') IS NOT NULL  DROP TABLE taGoCREATE TABLE ta(pid INT,tid INT,name NVARCHAR(6))GoINSERT INTO taSELECT 1,1,'hy3500' UNION ALLSELECT 1,2,'aabbcc' UNION ALLSELECT 2,3,'1111' UNION ALLSELECT 2,4,'2222' GO-- Test Data: tbIF OBJECT_ID('tb') IS NOT NULL  DROP TABLE tbGoCREATE TABLE tb(NAME NVARCHAR(2),id INT)GoINSERT INTO tb SELECT '型号',1 UNION ALL SELECT '参数',2 GO--StartSELECT t.[name],A,BFROM( SELECT B.[NAME], CAST((SELECT [name]                                                FROM TA                                                WHERE PID = A.PID FOR XML PATH('')) AS XML) AS X FROM TA A LEFT JOIN TB B ON A.PID = B.ID GROUP BY B.[NAME],A.PID) tCROSS APPLY (SELECT A=t.x.value('/name[1]','VARCHAR(10)'),B = t.x.value('/name[2]','VARCHAR(10)')) M --Result:/* c a b---- ---------- ----------参数 1111 2222型号 hy3500 aabbcc (2 行受影响) */--End

1. 取特定分隔符分隔的串中指定位置的串

-------------------------------------------------------------------------- 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 NT 5.0 (Build 2195: Service Pack 4)-- ------------------------------------------------------------------------ DECLARE @t TABLE(c VARCHAR(20))INSERT @t SELECT '双桥,9.6米,30.0吨'UNION ALL SELECT 'aa,bb,cc,dd'--通常情况如果项目在四个项目以内时，推荐一种方法：  SELECT REPLACE(PARSENAME(XX,3),'$$','.') C , REPLACE(PARSENAME(XX,2),'$$','.') B FROM( SELECT REPLACE(REPLACE(c,'.','$$'),',','.') XX FROM @T)AA--result/*c b --------------------------------- -----------双桥 9.6米 （所影响的行数为1 行）*/

好，那我们来看看下XML如何处理的

SELECT A,B FROM (SELECT CAST('<r>' + REPLACE(c,',','</r><r>') + '</r>' AS XML) x,c FROM @t) aCROSS APPLY (SELECT A=a.x.value('/r[1]','VARCHAR(10)'),B=a.x.value('/r[2]','VARCHAR(10)')) b /*A B---------- ----------双桥 9.6米aa bb (2 行受影响)*/

1. 把项目串的编码用相应名称代替

-------------------------------------------------------------------------- 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 NT 5.(Build 2195: Service Pack 4)-- ------------------------------------------------------------------------ -- Test Data: taIF OBJECT_ID('ta') IS NOT NULL  DROP TABLE taGoCREATE TABLE ta(ID NVARCHAR(3),number varchar(20))GoINSERT INTO ta SELECT '001','1,2' UNION ALL SELECT '002','1,2,3' GO-- Test Data: tbIF OBJECT_ID('tb') IS NOT NULL  DROP TABLE tbGoCREATE TABLE tb(ID INT,name NVARCHAR(7))GoINSERT INTO tbSELECT 1,'测试一' UNION ALLSELECT 2,'测试二' UNION ALLSELECT 3,'测试三' GO--Start--查询一SELECT C.ID,NUMBER = STUFF(REPLACE(REPLACE(( SELECT B.NAME AS NAME FROM TA A LEFT JOIN  ( SELECT ID,NAME FROM TB ) B  ON CHARINDEX(','+LTRIM(B.ID)+',',','+A.NUMBER+',')>0 WHERE A.ID = C.ID FOR XML AUTO ),'"/><B NAME="',','),'"/>',''),1,9,'')FROM TA C--查询二SELECT A.ID,NUMBER=STUFF( (SELECT ','+NAME  FROM TB  WHERE CHARINDEX(','+LTRIM(ID)+',',','+A.NUMBER+',')>0  FOR XML PATH(''),ROOT('R'),TYPE).value('/R[1]','NVARCHAR(MAX)') ,1,1,'') FROM TA A --Result:/*id number---- -----------------001 测试一,测试二002 测试一,测试二,测试三 (2 行受影响)*/--End

1. 最后我们来说说折分吧

如：

/*id value----------- -----------1 aa,bb2 aaa,bbb,ccc欲按id,分拆value列, 分拆后结果如下:id value----------- --------1 aa1 bb2 aaa2 bbb2 ccc*/

一般我们在下会借助中间生成一个连续的序列，然后和表关联折分，在下我们可借助CTE生成一个序列然后再拆分

-------------------------------------------------------------------------- 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 NT 5.0Build 2195: Service Pack 4)------------------------------------------------------------------------ CREATE TABLE TB(ID INT,VALUE VARCHAR(30))INSERT INTO TB VALUES(1,'AA,BB')INSERT INTO TB VALUES(2,'AAA,BBB,CCC')GOSELECT A.ID, B.VALUEFROM( SELECT ID,  [value] = CONVERT(XML,'<ROOT><V>' + REPLACE([VALUE], ',', '</V><V>') + '</V></ROOT>')  FROM tb)AOUTER APPLY( SELECT value = N.v.value('.', 'varchar(100)')  FROM A.[value].nodes('/ROOT/V') N(v))B DROP TABLE tb--查询结果/*id value----------- ------------------------------1 aa1 bb2 aaa2 bbb2 ccc (5 行受影响)*/

 

转载于:https://www.cnblogs.com/Traner/archive/2011/05/19/2819954.html