【SQL精彩语句】合并列值
1 --合并列值
2 --原著：邹建
3 --改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-16 广东深圳
4
5 --表结构，数据如下：
6 /*
7 id value
8 ----- ------
9 1 aa
10 1 bb
11 2 aaa
12 2 bbb
13 2 ccc
14 */
15 --需要得到结果：
16 /*
17 id values
18 ------ -----------
19 1 aa,bb
20 2 aaa,bbb,ccc
21 即：group by id, 求value 的和（字符串相加）
22 */
23 --1. 旧的解决方法(在sql server 2000中只能用函数解决。)
24 --1. 创建处理函数
25 create table tb(id int, value varchar(10))
26 insert into tb values(1, 'aa')
27 insert into tb values(1, 'bb')
28 insert into tb values(2, 'aaa')
29 insert into tb values(2, 'bbb')
30 insert into tb values(2, 'ccc')
31 go
32
33 create function dbo.f_str(@id int)
34 returns varchar(8000)
35 as
36 begin
37 declare @r varchar(8000)
38 set @r = ''
39 select @r = @r + ',' + value from tb where id=@id
40 return stuff(@r, 1, 1, '')
41 end
42 go
43
44 -- 调用函数
45 SELECt id, value = dbo.f_str(id) FROM tb GROUP BY id
46
47 drop table tb
48 drop function dbo.f_str
49
50 /*
51 id value
52 ----------- -----------
53 1 aa,bb
54 2 aaa,bbb,ccc
55 （所影响的行数为2 行）
56 */
57
58 --2、另外一种函数.
59 create table tb(id int, value varchar(10))
60 insert into tb values(1, 'aa')
61 insert into tb values(1, 'bb')
62 insert into tb values(2, 'aaa')
63 insert into tb values(2, 'bbb')
64 insert into tb values(2, 'ccc')
65 go
66
67 --创建一个合并的函数
68 create function f_hb(@id int)
69 returns varchar(8000)
70 as
71 begin
72 declare @str varchar(8000)
73 set @str = ''
74 select @str = @str + ',' + cast(value as varchar) from tb where id = @id
75 set @str = right(@str , len(@str) - 1)
76 return(@str)
77 End
78 go
79
80 --调用自定义函数得到结果：
81 select distinct id ,dbo.f_hb(id) as value from tb
82
83 drop table tb
84 drop function dbo.f_hb
85
86 /*
87 id value
88 ----------- -----------
89 1 aa,bb
90 2 aaa,bbb,ccc
91 （所影响的行数为2 行）
92 */
93
94 --2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)
95 create table tb(id int, value varchar(10))
96 insert into tb values(1, 'aa')
97 insert into tb values(1, 'bb')
98 insert into tb values(2, 'aaa')
99 insert into tb values(2, 'bbb')
100 insert into tb values(2, 'ccc')
101 go
102 -- 查询处理
103 select * from(select distinct id from tb)a outer apply(
104 select [values]= stuff(replace(replace(
105 (
106 select value from tb n
107 where id = a.id
108 for xml auto
109 ), ' <N value="', ','), '"/>', ''), 1, 1, '')
110 )N
111 drop table tb
112
113 /*
114 id values
115 ----------- -----------
116 1 aa,bb
117 2 aaa,bbb,ccc
118
119 (2 行受影响)
120 */
121
122 --SQL2005中的方法
123 create table tb(id int, value varchar(10))
124 insert into tb values(1, 'aa')
125 insert into tb values(1, 'bb')
126 insert into tb values(2, 'aaa')
127 insert into tb values(2, 'bbb')
128 insert into tb values(2, 'ccc')
129 go
130
131 select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
132 from tb
133 group by id
134
135 /*
136 id values
137 ----------- --------------------
138 1 aa,bb
139 2 aaa,bbb,ccc
140
141 (2 row(s) affected)
142
143 */
144
145 drop table tb
2 --原著：邹建
3 --改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-16 广东深圳
4
5 --表结构，数据如下：
6 /*
7 id value
8 ----- ------
9 1 aa
10 1 bb
11 2 aaa
12 2 bbb
13 2 ccc
14 */
15 --需要得到结果：
16 /*
17 id values
18 ------ -----------
19 1 aa,bb
20 2 aaa,bbb,ccc
21 即：group by id, 求value 的和（字符串相加）
22 */
23 --1. 旧的解决方法(在sql server 2000中只能用函数解决。)
24 --1. 创建处理函数
25 create table tb(id int, value varchar(10))
26 insert into tb values(1, 'aa')
27 insert into tb values(1, 'bb')
28 insert into tb values(2, 'aaa')
29 insert into tb values(2, 'bbb')
30 insert into tb values(2, 'ccc')
31 go
32
33 create function dbo.f_str(@id int)
34 returns varchar(8000)
35 as
36 begin
37 declare @r varchar(8000)
38 set @r = ''
39 select @r = @r + ',' + value from tb where id=@id
40 return stuff(@r, 1, 1, '')
41 end
42 go
43
44 -- 调用函数
45 SELECt id, value = dbo.f_str(id) FROM tb GROUP BY id
46
47 drop table tb
48 drop function dbo.f_str
49
50 /*
51 id value
52 ----------- -----------
53 1 aa,bb
54 2 aaa,bbb,ccc
55 （所影响的行数为2 行）
56 */
57
58 --2、另外一种函数.
59 create table tb(id int, value varchar(10))
60 insert into tb values(1, 'aa')
61 insert into tb values(1, 'bb')
62 insert into tb values(2, 'aaa')
63 insert into tb values(2, 'bbb')
64 insert into tb values(2, 'ccc')
65 go
66
67 --创建一个合并的函数
68 create function f_hb(@id int)
69 returns varchar(8000)
70 as
71 begin
72 declare @str varchar(8000)
73 set @str = ''
74 select @str = @str + ',' + cast(value as varchar) from tb where id = @id
75 set @str = right(@str , len(@str) - 1)
76 return(@str)
77 End
78 go
79
80 --调用自定义函数得到结果：
81 select distinct id ,dbo.f_hb(id) as value from tb
82
83 drop table tb
84 drop function dbo.f_hb
85
86 /*
87 id value
88 ----------- -----------
89 1 aa,bb
90 2 aaa,bbb,ccc
91 （所影响的行数为2 行）
92 */
93
94 --2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)
95 create table tb(id int, value varchar(10))
96 insert into tb values(1, 'aa')
97 insert into tb values(1, 'bb')
98 insert into tb values(2, 'aaa')
99 insert into tb values(2, 'bbb')
100 insert into tb values(2, 'ccc')
101 go
102 -- 查询处理
103 select * from(select distinct id from tb)a outer apply(
104 select [values]= stuff(replace(replace(
105 (
106 select value from tb n
107 where id = a.id
108 for xml auto
109 ), ' <N value="', ','), '"/>', ''), 1, 1, '')
110 )N
111 drop table tb
112
113 /*
114 id values
115 ----------- -----------
116 1 aa,bb
117 2 aaa,bbb,ccc
118
119 (2 行受影响)
120 */
121
122 --SQL2005中的方法
123 create table tb(id int, value varchar(10))
124 insert into tb values(1, 'aa')
125 insert into tb values(1, 'bb')
126 insert into tb values(2, 'aaa')
127 insert into tb values(2, 'bbb')
128 insert into tb values(2, 'ccc')
129 go
130
131 select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
132 from tb
133 group by id
134
135 /*
136 id values
137 ----------- --------------------
138 1 aa,bb
139 2 aaa,bbb,ccc
140
141 (2 row(s) affected)
142
143 */
144
145 drop table tb
