create table a(id varchar(4),name varchar(4))
insert into a select 'a','aa'
insert into a select 'b','bb'
insert into a select 'c','cc'
create table b(id int,name varchar(4),a_id varchar(4))
insert into b select 1,'11','a'
insert into b select 2,'22','a'
insert into b select 3,'33','b'
insert into b select 4,'44','c'
insert into b select 5,'55','a'
go
create function f_str(
@id varchar(4), --a表的id
@type int ) --类型：1、返回b表id的汇总；2、返回b表name的汇总
returns varchar(200)
as
begin
declare @ret varchar(200)
set @ret=''
select top 5 @ret=@ret+'$'+case @type when 1 then rtrim(id) when 2 then rtrim(name) end
from b where a_id=@id
set @ret=stuff(@ret,1,1,'')
return isnull(@ret,'')
end
go
select
id as a_id,
name as a_name,
dbo.f_str(id,1) as b_id,
dbo.f_str(id,2) as b_name
from
a
/*
a_id a_name b_id b_name
---- ------ -------------------- --------------------
a aa 1$2$5 11$22$55
b bb 3 33
c cc 4 44
*/
drop function f_str
drop table a,b
