原数据：1 a
1 b
2 a
2 b
2 c
2 d
3 a
3 b
3 c
3 d
需要得到的结果：
1 ab
2 abcd
3 abcd
谢谢
------解决方案--------------------
create table tb(id int,col char(1))
insert tb
select 1, 'a '
union all select 1, 'b '
union all select 2, 'a '
union all select 2, 'b '
union all select 2, 'c '
union all select 2, 'd '
union all select 3, 'a '
union all select 3, 'b '
union all select 3, 'c '
union all select 3, 'd '
go
create function fn_strunion(@id int)
returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re= ' '
select @[email protected]+col from tb where [email protected]
return @re
end
go
select id,col=dbo.fn_strunion(id) from tb group by id
drop table tb
drop function dbo.fn_strunion
------解决方案--------------------
create function fmerg(@col varchar(20))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str= ' '
select @[email protected]+cast(col2 as varchar) from t where [email protected]
group by col2
return(@str)
End
go
create table t(col1 int,col2 varchar(10))
insert t select 1, 'a '
union all select 1, 'b '
union all select 2, 'a '
union all select 2, 'b '
union all select 2, 'c '
union all select 2, 'd '
union all select 3, 'a '
union all select 3, 'b '
union all select 3, 'c '
union all select 3, 'd '
go
select distinct col1,col2=dbo.fmerg(col1) from t
drop table t
drop function dbo.fmerg
1 ab
2 abcd
3 abcd
（所影响的行数为 3 行）
------解决方案--------------------
楼上都正解,
2000下要用函数才能实现这种字符串聚合功能.
------解决方案--------------------
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(id varchar(10),name varchar(10))
insert into tb(id,name) values( '1 ', 'a ')
insert into tb(id,name) values( '1 ', 'b ')
insert into tb(id,name) values( '2 ', 'a ')
insert into tb(id,name) values( '2 ', 'b ')
insert into tb(id,name) values( '2 ', 'c ')
insert into tb(id,name) values( '2 ', 'd ')