create table 表A
(Aid int, Aname varchar(10), Abeizhu varchar(5))

insert into 表A
select 1, 'Aname1', 'Abz1' union all
select 2, 'aname2', 'Abz2' union all
select 3, 'Aname3', 'Abz3'

create table 表B
(Bid int, Bname varchar(10), Bbeizhu varchar(5), Aid int)
  
insert into 表B
select 1, 'Bname1', 'Bbz1', 1 union all
select 2, 'Bname2', 'Bbz2', 1 union all
select 3, 'Bname3', 'Bbz3', 2 union all
select 4, 'Bname1', 'Bbz4', 2 union all
select 5, 'Bname2', 'Bbz5', 3 union all
select 6, 'Bname3', 'Bbz6', 3


select a.Aid,a.Aname,a.Abeizhu,
b.beizhu
from 表A a
inner join 
(select Aid,
cast((select Bbeizhu+';' from 表B b1 where b1.Aid=b0.Aid for xml path('')) as varchar) beizhu
from 表B b0
group by Aid) b
on a.Aid=b.Aid

Aid         Aname      Abeizhu beizhu
----------- ---------- ------- ------------------------------
1           Aname1     Abz1    Bbz1;Bbz2;
2           aname2     Abz2    Bbz3;Bbz4;
3           Aname3     Abz3    Bbz5;Bbz6;

(3 row(s) affected)