create table Class(id varchar(50), class varchar(50))
create table Student(id varchar(50), name varchar(50))
insert into class(id, class) values('001', '一班')
insert into class(id, class) values('002', '二班')
insert into Student(id, name) values('001', '张三')
insert into Student(id, name) values('001', '李四')
insert into Student(id, name) values('002', '张s三')
insert into Student(id, name) values('002', '李s四')
go
create function GetName(@id varchar(100))
returns nvarchar(4000)
as
begin
declare @s varchar(4000)
set @s = ''
select @s = @s + rtrim(name) +',' from student where id = @id
set @s = left(@s, len(@s) -1)
return @s
end
go
select a.id ,a.class, dbo.getname(a.id) from class a
group by a.id,a.class
drop table class
drop table student
drop function getname
先建一个合并的函数
create function getstr(@id Nchar(3))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+rtrim(student) from 从表 where id=@id
if Len(@str) > 0
set @str=left(@str,len(@str)-1)
return @str
end
GO
2,调用自定义函数得到结果
select distinct id, class, dbo.getstr(id) as names from 主表
create function getstr(@id Nchar(4000))
returns Nvarchar(4000)
as
begin
declare @str Nvarchar(2000)
set @str=N''
select @str=@str+rtrim(student) from 从表
where id=@id
if @str<>N''
set @str=left(@str,len(@str)-1)
return @str
end
GO
2,
select id, class, dbo.getstr(id) as names from 主表 group by id