create function f_str(@id varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+'|'+[科目] from B where id=@id
if @str is null
return ''
return stuff(@str,1,1,'')
end
go--查询
select A.[姓名],
[科目]=dbo.f_str(A.id),
[科目数]=T.num
from A
left join
(
select id,[num]=count(1)
from B group by id
)T on A.id=T.id
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+'|'+[科目] from B where id=@id
if @str is null
return ''
return stuff(@str,1,1,'')
end
go--查询
select A.[姓名],
[科目]=dbo.f_str(A.id),
[科目数]=T.num
from A
left join
(
select id,[num]=count(1)
from B group by id
)T on A.id=T.id
(id1 int ,id2 int ,id3 char(10))insert into t
select 1,2,1
union
select 1,2,2
union
select 1,3,1
union
select 1,3,2
union
select 1,4,2
create function f_str1(@id1 int,@id2 int)
returns varchar(1000)
as
begin
declare @r varchar(1000)
set @r = ''
select @r = @r+','+rtrim(id3) from t where id1=@id1 and id2=@id2
return(stuff(@r,1,1,''))
endselect id1,id2,dbo.f_str1(id1,id2) from t group by id1,id2
你参考一下吧