一个sql简单函数实现拼接字符串

--测试数据
create table table1(AID int,NAME nvarchar(20))
create table table2 (BID int,NUMBER nvarchar(20))
insert into table1 select 1,'Tom' union all
select 2,'Jim'
insert into table2 select 1,20 union all
select 1,30
--函数
create function F_Str(@ID int)
returns nvarchar(100)

as

begin
   
    declare @S nvarchar(100)
    select return @S=isnull(@S+',','')+NUMBER from table2 where BID=@ID
   return @S
end
--查询
select NAME,NUMBER = dbo.F_Str(AID) from table1
--结果
-------------------
Tom    20,30
Jim    NULL