create function fmerge(@b int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+姓名 from 表 where 班级=@b
set @str=right(@str,len(@str)-1)
return(@str)
End
用函数实现:
create function funlink(@id int)
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s+rtrim(name)+',' from table where id=@id
return @s
end
select 班级,funlink(@id) as 人员 from table group by id
看看这个例子吧!
create table ta (id int, value varchar(20))
Insert into ta
select '1','aaa'
union all select '2','bbb'
union all select '3','ccc'
create table tb (id int, value varchar(20))
Insert into tb
select '1','aaa,bbb,'
union all select '2','bbb,ccc,'
union all select '3','aaa,bbb,ccc,'
select * from ta
select * from tb
--函數
create function dbo.fn_m(@vchstring varchar(1000))
returns varchar(1000)
as
begin
declare @intstart int,@intlocation int
declare @s varchar(1000),@vchsubstring varchar(100)
select @intstart =1,@s=''
select @intlocation = charindex(',',@vchstring,@intstart)
while (@intlocation <>0 )
begin
select @vchsubstring=substring(@vchstring,@intstart,@intlocation-@intstart)
select @s=@s+cast([id] as varchar)+',' from ta where value=@vchsubstring
select @intstart = @intlocation +1
select @intlocation = charindex(',',@vchstring,@intstart)
end
return(@s)
end
--刪除
drop table ta
drop table tb
drop function dbo.fn_m
select id, value=dbo.fn_m(value) from tb
--結果
id value
------------------
1 1,2,
2 2,3,
3 1,2,3,
create function fmerge(@b int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+姓名 from 表 where 班级=@b
set @str=right(@str,len(@str)-1)
return(@str)
End