create function f_str(@user_name varchar(50))
returns varchar(500)
as
begin
    declare @ret varchar(500)
    set @ret = ''
    select @ret = @ret + g from c1 where bid=@user_name
    set @ret = stuff(@ret,1,1,'')
    return @ret
end
go

select b.*,dbo.f_str(bid)
from b1 b
group by bid,aid,d,e

------解决方案--------------------
SQL code

select
    distinct b.bid ,      b.aid         b.d  ,[g]=dbo.f_test(g)
from 
    b1 b
join
    c1 c on b.bid=c.bid
where
    
    b.aid='001'