create table tb(ID int,NAME varchar(10))
insert into tb
select 1,'w' union all
select 2,'e'
go
create table tt(ID int,inf varchar(10))
insert into tt
select 1,'ttt' union all
select 1,'rrr'
go
create function dbo.fc_str(@id varchar(100))
returns varchar(100)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+cast(inf as varchar(100)) from tt where id=@id
return stuff(@sql,1,1,'')
end
go
select a.name,dbo.fc_str(b.id) as inf from tb a,tt b where a.id=b.id group by b.id,a.name
create function f_union(@id int)
returns varchar(100)
as
begin
declare @sql varchar(100)
set @sql=''
select @sql=@sql+','+info from 表2 where id=@id
return(stuff(@sql,1,1,''))
end
go
select [name],info=dbo.f_union(id) from 表1,表2
where 表1.id=表2.id group by 表2.id