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
endGO2,
select id, class, dbo.getstr(id) as names from 主表 group by id愉快的登山者 ⊙
◢◣◢◣◢◣
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
endGO2,
select id, class, dbo.getstr(id) as names from 主表 group by id愉快的登山者 ⊙
◢◣◢◣◢◣
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
endGO
2,调用自定义函数得到结果
select distinct id, class, dbo.getstr(id) as names from 主表
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四')
gocreate 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
goselect a.id ,a.class, dbo.getname(a.id) from class a
group by a.id,a.classdrop table class
drop table student
drop function getname