SQL中合并分拆表方法汇总

  dobear_0922 兄弟整理的方法如下：

1. -- =============================================================================
2. -- Title: 在SQL中分类合并数据行
3. -- Author: dobear        Mail(MSN): dobear_0922@hotmail.com
4. -- Environment: Vista + SQL2005
5. -- Date: 2008-04-22
6. -- =============================================================================
8. --1. 创建表，添加测试数据
9. CREATE TABLE tb(id int, [value] varchar(10))
10. INSERT tb SELECT 1, 'aa'
11. UNION ALL SELECT 1, 'bb'
12. UNION ALL SELECT 2, 'aaa'
13. UNION ALL SELECT 2, 'bbb'
14. UNION ALL SELECT 2, 'ccc'
16. --SELECT * FROM tb
17. /**//*
18. id          value
19. ----------- ----------
20. 1           aa
21. 1           bb
22. 2           aaa
23. 2           bbb
24. 2           ccc
26. (5 row(s) affected)
27. */
30. --2 在SQL2000只能用自定义函数实现
31. ----2.1 创建合并函数fn_strSum，根据id合并value值
32. GO
33. CREATE FUNCTION dbo.fn_strSum(@id int)
34. RETURNS varchar(8000)
35. AS
36. BEGIN
37.     DECLARE @values varchar(8000)
38.     SET @values = ''
39.     SELECT @values = @values + ',' + value FROM tb WHERE id=@id
40.     RETURN STUFF(@values, 1, 1, '')
41. END
42. GO
44. -- 调用函数
45. SELECT id, VALUE = dbo.fn_strSum(id) FROM tb GROUP BY id
46. DROP FUNCTION dbo.fn_strSum
48. ----2.2 创建合并函数fn_strSum2，根据id合并value值
49. GO
50. CREATE FUNCTION dbo.fn_strSum2(@id int)
51. RETURNS varchar(8000)
52. AS
53. BEGIN
54.     DECLARE @values varchar(8000)    
55.     SELECT @values = isnull(@values + ',', '') + value FROM tb WHERE id=@id
56.     RETURN @values
57. END
58. GO
60. -- 调用函数
61. SELECT id, VALUE = dbo.fn_strSum2(id) FROM tb GROUP BY id
62. DROP FUNCTION dbo.fn_strSum2
65. --3 在SQL2005中的新解法
66. ----3.1 使用OUTER APPLY
67. SELECT * 
68. FROM (SELECT DISTINCT id FROM tb) A OUTER APPLY(
69.         SELECT [values]= STUFF(REPLACE(REPLACE(
70.             (
71.                 SELECT value FROM tb N
72.                 WHERE id = A.id
73.                 FOR XML AUTO
74.             ), '<N value="', ','), '"/>', ''), 1, 1, '')
75. )N
77. ----3.2 使用XML
78. SELECT id, [values]=STUFF((SELECT ','+[value] FROM tb t WHERE id=tb.id FOR XML PATH('')), 1, 1, '')
79. FROM tb
80. GROUP BY id
82. --4 删除测试表tb
83. drop table tb
85. /**//*
86. id          values
87. ----------- --------------------
88. 1           aa,bb
89. 2           aaa,bbb,ccc
91. (2 row(s) affected)
92. */

 

roy_88 兄弟整理的方法：

1. --合并分拆表
2. /******************************************************************************************************************************************************
3. 合并分拆表数据
5. 整理人：中国风(Roy)
7. 日期:2008.06.06
8. ******************************************************************************************************************************************************/
10. --> --> (Roy)生成測試數據
12. if not object_id('Tab') is null
13.     drop table Tab
14. Go
15. Create table Tab([Col1] int,[Col2] nvarchar(1))
16. Insert Tab
17. select 1,N'a' union all
18. select 1,N'b' union all
19. select 1,N'c' union all
20. select 2,N'd' union all
21. select 2,N'e' union all
22. select 3,N'f'
23. Go
25. 合并表:
27. SQL2000用函数:
29. go
30. if object_id('F_Str') is not null
31.     drop function F_Str
32. go
33. create function F_Str(@Col1 int)
34. returns nvarchar(100)
35. as
36. begin
37.     declare @S nvarchar(100)
38.     select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1
39.     return @S
40. end
41. go
42. Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab
44. go
46. SQL2005用XML:
48. 方法1:
50. select 
51.     a.Col1,Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'')
52. from 
53.     (select distinct COl1 from Tab) a
54. Cross apply
55.     (select COl2=(select N','+Col2 from Tab where Col1=a.COl1 For XML PATH(''), ROOT('R'), TYPE))b
57. 方法2:
59. select 
60.     a.Col1,COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44))
61. from 
62.     (select distinct COl1 from Tab) a
63. cross apply
64.     (select Col2=(select COl2 from Tab  where COl1=a.COl1 FOR XML AUTO, TYPE)
65.                 .query('<Tab>
66.                 {for $i in /Tab[position()<last()]/@COl2 return concat(string($i),",")}
67.                 {concat("",string(/Tab[last()]/@COl2))}
68.                 </Tab>')
69.                 )b
71. SQL05用CTE:
73. ;with roy as(select Col1,Col2,row=row_number()over(partition by COl1 order by COl1) from Tab)
74. ,Roy2 as
75. (select COl1,cast(COl2 as nvarchar(100))COl2,row from Roy where row=1 
76. union all 
77. select a.Col1,cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row from Roy a join Roy2 b on a.COl1=b.COl1 and a.row=b.row+1)
78. select Col1,Col2 from Roy2 a where row=(select max(row) from roy where Col1=a.COl1) order by Col1 option (MAXRECURSION 0)
81. 生成结果:
82. /*
83. Col1        COl2
84. ----------- ------------
85. 1           a,b,c
86. 2           d,e
87. 3           f
89. (3 行受影响)
90. */
93. 拆分表:
95. --> --> (Roy)生成測試數據
97. if not object_id('Tab') is null
98.     drop table Tab
99. Go
100. Create table Tab([Col1] int,[COl2] nvarchar(5))
101. Insert Tab
102. select 1,N'a,b,c' union all
103. select 2,N'd,e' union all
104. select 3,N'f'
105. Go
107. SQL2000用辅助表:
108. if object_id('Tempdb..#Num') is not null
109.     drop table #Num
110. go
111. select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
112. Select 
113.     a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) 
114. from 
115.     Tab a,#Num b
116. where
117.     charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','
120. SQL2005用Xml:
122. select 
123.     a.COl1,b.Col2
124. from 
125.     (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a
126. outer apply
127.     (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
132. SQL05用CTE:
134. ;with roy as 
135. (select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab
136. union all
137. select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
138. )
139. select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)
141. 生成结果:
142. /*
143. Col1        COl2
144. ----------- -----
145. 1           a
146. 1           b
147. 1           c
148. 2           d
149. 2           e
150. 3           f
151. */

更多方法

http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html

http://topic.csdn.net/u/20080510/13/8ca706fd-3c13-417a-ace8-6ec2a0cd182a.html