若组别不只一个,如下调用:
declare @str varchar(100),@id int,@groupn varchar(10)
set @str = '教务处、阿水、总务处'
declare cur cursor for
Select [id], groupname from 表2 where charindex(groupname,@str) > 0
open cur
fetch next from cur into @id,@groupn
while @@fetch_status = 0 and @@rowcount>0
begin
set @str = replace(@str,@groupn,dbo.fmerg(@id))
fetch next from cur into @id,@groupn
end
close cur
deallocate cur
select @str
--创建一个合并的函数,@gid为组号,@spcchr为姓名之间的分隔符
create function f_merg(@gid int,@spcchr varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+@spcchr+[user] from 表A where groupid=@gid
set @str=substring(@str,len(@spcchr)+1,8000)
return(@str)
End
go
--调用自定义函数得到结果,名称之间用空格分隔
select groupid,dbo.f_merg(groupid,' ') from 表A group by groupid
create table 表1(id int,groupid int,[user] varchar(10))
insert 表1
Select 1,1,'阿明'
Union all select 2,1,'阿江'
Union all select 3,2,'阿水'
Union all select 4,2,'阿狂'
create table 表2 (id int,groupname varchar(10))
insert 表2 select 1,'教务处'
Union all select 2,'总务处'
--1.创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+'、'+[user] from 表1 where groupid=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go
declare @str varchar(100),@id int,@groupn varchar(10)
set @str = '教务处、阿水'
Select @id = [id],@groupn = groupname from 表2 where charindex(groupname,@str) > 0
Select replace(@str,@groupn,dbo.fmerg(@id))
--1.创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+'、'+[user] from 表A where groupid=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go
--调用自定义函数得到结果
select distinct a.groupid,dbo.fmerg(a.groupid) from 表A a
join 表B b on a.groupid = b.id where b.groupname = '教务处'
--1.创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+'、'+[user] from 表A where groupid=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go
--调用自定义函数得到结果
select distinct groupid,dbo.fmerg(groupid) from 表A