寻求一个语句
原数据：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 @re=@re+col from tb where id=@id
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 @str=@str+cast(col2 as varchar) from t where col1=@col
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 ',