寻求一个语句
原数据：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 ', 'd ')
insert into tb(id,name) values( '3 ', 'a ')
insert into tb(id,name) values( '3 ', 'b ')
insert into tb(id,name) values( '3 ', 'c ')
insert into tb(id,name) values( '3 ', 'd ')
go
if object_id( 'pubs..f_hb ') is not null
drop function f_hb
go
--创建一个合并的函数
create function f_hb(@id varchar)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + cast(name as varchar) from tb where id = @id
set @str = right(@str , len(@str))
return(@str)
End
go
--调用自定义函数得到结果：
select distinct id ,dbo.f_hb(id) as name from tb
drop table tb
/*
id name
---------- ----
1 ab
2 abcd
3 abcd
（所影响的行数为 3 行）
*/
