--创建用户定义函数
create function f_str(@id int)
returns varchar(8000)
as
begin
declare @v varchar(8000)
set @v = ''
select @v = @v + ','+ name from 表b where id = @id
set @v = stuff(@v,1,1,'')
return @v
end
go--执行查询
select id,name,name1=dbo.f_str(id) from 表a group by id,name
go
create function f_str(@id int)
returns varchar(8000)
as
begin
declare @v varchar(8000)
set @v = ''
select @v = @v + ','+ name from 表b where id = @id
set @v = stuff(@v,1,1,'')
return @v
end
go--执行查询
select id,name,name1=dbo.f_str(id) from 表a group by id,name
go
insert into 表a select 1,'zxz'
create table 表b(id int,parent_id int,name varchar(10))
insert into 表b select 1,1,'www'
insert into 表b select 2,1,'ccc'
insert into 表b select 3,1,'uuu'--创建用户定义函数
create function f_str(@id int)
returns varchar(8000)
as
begin
declare @v varchar(8000)
set @v = ''
select @v = @v + ';'+ name from 表b where parent_id = @id
set @v = stuff(@v,1,1,'')
return @v
end
go--执行查询
select id,name,name1=dbo.f_str(id) from 表a group by id,name
go--输出结果
id name name1
-- ---- -----------
1 zxz www;ccc;uuu
returns varchar(4000)
as
begin
declare @dd varchar(4000)
set @dd=''
select @dd=@dd+':'+name from b where parent_id=@parent_id
return(stuff(@dd,1,1,''))
endselect c.id,a.name name,c.name name1 from (select parent_id id,name=dbo.ff(parent_id) from b group by parent_id) c join a
on a.id=c.id