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

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

作者：胡林

时间：2009年1月4日

转载请注明出处

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

  1. 先来一个简单点，如下的例子对aaa相同的项目合并。
  1. ------------------------------------------------------------------------
  2. -- Author: HappyFlyStone 
  3. -- Date : 2009-01-04 20:57:59
  4. -- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) 
  5. -- Apr 14 2006 01:12:25 
  6. -- Copyright (c) 1988-2005 Microsoft Corporation
  7. -- Standard Edition on Windows NT5.0(Build 2195: Service Pack 4)
  8. -- 
  9. ------------------------------------------------------------------------
  10. -- Test Data: tb
  11. IF OBJECT_ID('tb') IS NOT NULL 
  12.  DROP TABLE tb
  13. Go
  14. CREATE TABLE tb(aaa INT,bbb INT)
  15. Go
  16. INSERT INTO tb
  17. SELECT 1,2 UNION ALL
  18. SELECT 1,3 UNION ALL
  19. SELECT 1,4 UNION ALL
  20. SELECT 2,2 UNION ALL
  21. SELECT 2,5 
  22. GO
  23.  
  24. --查询1
  25. select  aaa,[values]=stuff(replace(replace((select [bbb] 
  26.  from tb 
  27.  where aaa=t.aaa for xml AUTO), '"/><tb bbb="',','), '"/>',''),1,9,'')
  28. from tb t
  29. group by aaa 
  30.  
  31. --查询2
  32. SELECT * FROM( 
  33.  SELECT DISTINCT aaa 
  34.  FROM tb 
  35.  ) A 
  36.  OUTER APPLY( 
  37.  SELECT [bbb]= STUFF(REPLACE(REPLACE( 
  38.   ( SELECT [bbb] FROM tb N 
  39.   WHERE aaa = A.aaa 
  40.   FOR XML AUTO 
  41.    ), '<N bbb="', ','), '"/>', ''), 1, 1, '') 
  42.  ) N 
  43.  
  44. --查询3
  45. select aaa,[values]=stuff((select ','+ltrim([bbb])
  46.  from tb t 
  47.  where aaa=tb.aaa for xml path('')), 1, 1, '') 
  48. from tb 
  49. group by aaa 
  50.  
  51.  
  52. drop table tb
  53.  
  54. --查询结果
  55. /*
  56. aaa values
  57. ----------- ---------
  58. 1 2,3,4
  59. 2 2,5
  61. (2 行受影响)
  63. */
  1. 来个两个表关联操作并实现行列转换的
  1. ------------------------------------------------------------------------
  2. -- Author: HappyFlyStone 
  3. -- Date : 2009-01-04 20:57:59
  4. -- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) 
  5. -- Apr 14 2006 01:12:25 
  6. -- Copyright (c) 1988-2005 Microsoft Corporation
  7. -- Standard Edition on Windows NT5.0(Build 2195: Service Pack 4)
  8. -- 
  9. ------------------------------------------------------------------------
  10.  
  11. -- Test Data: ta
  12. IF OBJECT_ID('ta') IS NOT NULL 
  13.  DROP TABLE ta
  14. Go
  15. CREATE TABLE ta(pid INT,tid INT,name NVARCHAR(6))
  16. Go
  17. INSERT INTO ta
  18. SELECT 1,1,'hy3500' UNION ALL
  19. SELECT 1,2,'aabbcc' UNION ALL
  20. SELECT 2,3,'1111' UNION ALL
  21. SELECT 2,4,'2222' 
  22. GO
  23. -- Test Data: tb
  24. IF OBJECT_ID('tb') IS NOT NULL 
  25.  DROP TABLE tb
  26. Go
  27. CREATE TABLE tb(NAME NVARCHAR(2),id INT)
  28. Go
  29. INSERT INTO tb
  30.  SELECT '型号',1 UNION ALL
  31.  SELECT '参数',2 
  32. GO
  33. --Start
  34. SELECT t.[name],A,B
  35. FROM(
  36.  SELECT B.[NAME], CAST((SELECT [name] 
  37.                                                FROM TA 
  38.                                                WHERE PID = A.PID FOR XML PATH('')) AS XML) AS X
  39.  FROM TA A
  40.  LEFT JOIN TB B ON A.PID = B.ID
  41.  GROUP BY B.[NAME],A.PID
  42. ) t
  43. CROSS APPLY
  44.  (SELECT A=t.x.value('/name[1]','VARCHAR(10)'),B = t.x.value('/name[2]','VARCHAR(10)')) M
  45.  
  46. --Result:
  47. /*
  48.  
  49. c a b
  50. ---- ---------- ----------
  51. 参数 1111 2222
  52. 型号 hy3500 aabbcc
  53.  
  54. (2 行受影响)
  55.  
  56. */
  57. --End
  1. 取特定分隔符分隔的串中指定位置的串
  1. ------------------------------------------------------------------------
  2. -- Author: HappyFlyStone 
  3. -- Date : 2009-01-04 20:57:59
  4. -- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) 
  5. -- Apr 14 2006 01:12:25 
  6. -- Copyright (c) 1988-2005 Microsoft Corporation
  7. -- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
  8. -- 
  9. ------------------------------------------------------------------------
  10.  
  11. DECLARE @t TABLE(c VARCHAR(20))
  12. INSERT @t SELECT '双桥,9.6米,30.0吨'
  13. UNION ALL SELECT 'aa,bb,cc,dd'
  14. --通常情况如果项目在四个项目以内时，推荐一种方法：
  15.  
  16.  
  17. SELECT REPLACE(PARSENAME(XX,3),'$$','.') C ,
  18.  REPLACE(PARSENAME(XX,2),'$$','.') B 
  19. FROM
  20. (
  21.  SELECT REPLACE(REPLACE(c,'.','$$'),',','.') XX FROM @T
  22. )AA
  23. --result
  24. /*
  25. c b 
  26. --------------------------------- -----------
  27. 双桥 9.6米
  28.  
  29. （所影响的行数为1 行）
  30. */

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

  1. SELECT A,B FROM
  2.  (SELECT CAST('<r>' + REPLACE(c,',','</r><r>') + '</r>' AS XML) x,c FROM @t) a
  3. CROSS APPLY
  4.  (SELECT A=a.x.value('/r[1]','VARCHAR(10)'),B=a.x.value('/r[2]','VARCHAR(10)')) b
  5.  
  6. /*
  7. A B
  8. ---------- ----------
  9. 双桥 9.6米
  10. aa bb
  11.  
  12. (2 行受影响)
  13. */
  1. 把项目串的编码用相应名称代替
  1. ------------------------------------------------------------------------
  2. -- Author: HappyFlyStone 
  3. -- Date : 2009-01-04 20:57:59
  4. -- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) 
  5. -- Apr 14 2006 01:12:25 
  6. -- Copyright (c) 1988-2005 Microsoft Corporation
  7. -- Standard Edition on Windows NT 5.(Build 2195: Service Pack 4)
  8. -- 
  9. ------------------------------------------------------------------------
  10.  
  11. -- Test Data: ta
  12. IF OBJECT_ID('ta') IS NOT NULL 
  13.  DROP TABLE ta
  14. Go
  15. CREATE TABLE ta(ID NVARCHAR(3),number varchar(20))
  16. Go
  17. INSERT INTO ta
  18.  SELECT '001','1,2' UNION ALL
  19.  SELECT '002','1,2,3' 
  20. GO
  21. -- Test Data: tb
  22. IF OBJECT_ID('tb') IS NOT NULL 
  23.  DROP TABLE tb
  24. Go
  25. CREATE TABLE tb(ID INT,name NVARCHAR(7))
  26. Go
  27. INSERT INTO tb
  28. SELECT 1,'测试一' UNION ALL
  29. SELECT 2,'测试二' UNION ALL
  30. SELECT 3,'测试三' 
  31. GO
  32. --Start
  33. --查询一
  34. SELECT C.ID,NUMBER = STUFF(REPLACE(REPLACE((
  35.  SELECT B.NAME AS NAME
  36.  FROM TA A
  37.  LEFT JOIN 
  38.  ( SELECT ID,NAME
  39.  FROM TB
  40.  ) B 
  41.  ON CHARINDEX(','+LTRIM(B.ID)+',',','+A.NUMBER+',')>0
  42.  WHERE A.ID = C.ID
  43.  FOR XML AUTO
  44.  ),'"/><B NAME="',','),'"/>',''),1,9,'')
  45. FROM TA C
  46. --查询二
  47. SELECT A.ID,NUMBER=STUFF(
  48.  (SELECT ','+NAME 
  49.  FROM TB 
  50.  WHERE CHARINDEX(','+LTRIM(ID)+',',','+A.NUMBER+',')>0 
  51.  FOR XML PATH(''),ROOT('R'),TYPE).value('/R[1]','NVARCHAR(MAX)')
  52.  ,1,1,'') 
  53. FROM TA A
  54.  
  55. --Result:
  56. /*
  57. id number
  58. ---- -----------------
  59. 001 测试一,测试二
  60. 002 测试一,测试二,测试三
  61.  
  62. (2 行受影响)
  63. */
  64. --End
  1. 最后我们来说说折分吧

如：

  1. /*
  2. id value
  3. ----------- -----------
  4. 1 aa,bb
  5. 2 aaa,bbb,ccc
  6. 欲按id,分拆value列, 分拆后结果如下:
  7. id value
  8. ----------- --------
  9. 1 aa
  10. 1 bb
  11. 2 aaa
  12. 2 bbb
  13. 2 ccc
  14. */

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

  1. ------------------------------------------------------------------------
  2. -- Author: HappyFlyStone 
  3. -- Date : 2009-01-04 20:57:59
  4. -- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) 
  5. -- Apr 14 2006 01:12:25 
  6. -- Copyright (c) 1988-2005 Microsoft Corporation
  7. -- Standard Edition on Windows NT 5.0Build 2195: Service Pack 4)
  8. ------------------------------------------------------------------------
  9.  
  10. CREATE TABLE TB(ID INT,VALUE VARCHAR(30))
  11. INSERT INTO TB VALUES(1,'AA,BB')
  12. INSERT INTO TB VALUES(2,'AAA,BBB,CCC')
  13. GO
  14. SELECT A.ID, B.VALUE
  15. FROM(
  16.  SELECT ID, 
  17.  [value] = CONVERT(XML,'<ROOT><V>' + REPLACE([VALUE], ',', '</V><V>') + '</V></ROOT>') 
  18.  FROM tb
  19. )A
  20. OUTER APPLY(
  21.  SELECT value = N.v.value('.', 'varchar(100)') 
  22.  FROM A.[value].nodes('/ROOT/V') N(v)
  23. )B
  24.  
  25. DROP TABLE tb
  26. --查询结果
  27. /*
  28. id value
  29. ----------- ------------------------------
  30. 1 aa
  31. 1 bb
  32. 2 aaa
  33. 2 bbb
  34. 2 ccc
  35.  
  36. (5 行受影响)
  37. */

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/Traner/p/2819954.html