/*
标题:按某字段合并字符串之一(简单合并)
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-11-06
地点:广东深圳
描述:将如下形式的数据按id字段合并value字段。
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果:
id value
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即:group by id, 求 value 的和(字符串相加)
*/
--
1、sql2000中只能用自定义的函数解决
create
table
tb(id
int
, value
varchar
(
10
))
insert
into
tb
values
(
1
,
'
aa
'
)
insert
into
tb
values
(
1
,
'
bb
'
)
insert
into
tb
values
(
2
,
'
aaa
'
)
insert
into
tb
values
(
2
,
'
bbb
'
)
insert
into
tb
values
(
2
,
'
ccc
'
)
go
create
function
dbo.f_str(
@id
varchar
(
10
))
returns
varchar
(
1000
)
as
begin
declare
@str
varchar
(
1000
)
select
@str
=
isnull
(
@str
+
'
,
'
,
''
)
+
cast
(value
as
varchar
)
from
tb
where
id
=
@id
return
@str
end
go
--
调用函数
select
id , value
=
dbo.f_str(id)
from
tb
group
by
id
drop
function
dbo.f_str
drop
table
tb
--
2、sql2005中的方法
create
table
tb(id
int
, value
varchar
(
10
))
insert
into
tb
values
(
1
,
'
aa
'
)
insert
into
tb
values
(
1
,
'
bb
'
)
insert
into
tb
values
(
2
,
'
aaa
'
)
insert
into
tb
values
(
2
,
'
bbb
'
)
insert
into
tb
values
(
2
,
'
ccc
'
)
go
select
id,
[
value
]
=
stuff
((
select
'
,
'
+
[
value
]
from
tb t
where
id
=
tb.id
for
xml path(
''
)) ,
1
,
1
,
''
)
from
tb
group
by
id
drop
table
tb
--
3、使用游标合并数据
create
table
tb(id
int
, value
varchar
(
10
))
insert
into
tb
values
(
1
,
'
aa
'
)
insert
into
tb
values
(
1
,
'
bb
'
)
insert
into
tb
values
(
2
,
'
aaa
'
)
insert
into
tb
values
(
2
,
'
bbb
'
)
insert
into
tb
values
(
2
,
'
ccc
'
)
go
declare
@t
table
(id
int
,value
varchar
(
100
))
--
定义结果集表变量
--
定义游标并进行合并处理
declare
my_cursor
cursor
local
for
select
id , value
from
tb
declare
@id_old
int
,
@id
int
,
@value
varchar
(
10
) ,
@s
varchar
(
100
)
open
my_cursor
fetch
my_cursor
into
@id
,
@value
select
@id_old
=
@id
,
@s
=
''
while
@@FETCH_STATUS
=
0
begin
if
@id
=
@id_old
select
@s
=
@s
+
'
,
'
+
cast
(
@value
as
varchar
)
else
begin
insert
@t
values
(
@id_old
,
stuff
(
@s
,
1
,
1
,
''
))
select
@s
=
'
,
'
+
cast
(
@value
as
varchar
) ,
@id_old
=
@id
end
fetch
my_cursor
into
@id
,
@value
END
insert
@t
values
(
@id_old
,
stuff
(
@s
,
1
,
1
,
''
))
close
my_cursor
deallocate
my_cursor
select
*
from
@t
drop
table
tb