create table tb(id int,txt varchar(100))
go
insert into tb
select 1,'aaa' union all
select 1,'bbb' union all
select 2,'ccc' union all
select 3,'ddd' union all
select 3,'eee' union all
select 3,'fff'
go
--写一个聚合函数:
create function dbo.fn_Merge(@id int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+';'+txt from tb where id=@id
return stuff(@r,1,1,'')
end
go
-- 调用函数
select id, dbo.fn_Merge(id) as txt from tb group by id
go
drop table tb
drop function fn_Merge
这条语句在2005是能执行出来的,可是2000里确不行,大家给看看,大概意思就是,我要得到 下面的结果
id txt
1 aaa
1 bbb
2 ccc
3 ddd
3 eee
3 fff
select id,***(txt,';') from tb group by id
结果:
1 aaa;bbb
2 ccc
3 ddd;eee;fff
go
insert into tb
select 1,'aaa' union all
select 1,'bbb' union all
select 2,'ccc' union all
select 3,'ddd' union all
select 3,'eee' union all
select 3,'fff'
go
--写一个聚合函数:
create function dbo.fn_Merge(@id int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+';'+txt from tb where id=@id
return stuff(@r,1,1,'')
end
go
-- 调用函数
select id, dbo.fn_Merge(id) as txt from tb group by id
go
drop table tb
drop function fn_Merge
这条语句在2005是能执行出来的,可是2000里确不行,大家给看看,大概意思就是,我要得到 下面的结果
id txt
1 aaa
1 bbb
2 ccc
3 ddd
3 eee
3 fff
select id,***(txt,';') from tb group by id
结果:
1 aaa;bbb
2 ccc
3 ddd;eee;fff
看提示就知道了,CREATE要为批处理的开始。