求1个sql查询语句解决方案

求1个sql查询语句
表A 
 Aid Aname Abeizhu
  1 Aname1 Abz1
  2 aname2 Abz2
  3 Aname3 Abz3
表B
  Bid Bname Bbeizhu Aid
  1 Bname1 Bbz1 1
  2 Bname2 Bbz2 1
  3 Bname3 Bbz3 2
  4 Bname1 Bbz4 2
  5 Bname2 Bbz5 3
  6 Bname3 Bbz6 3

如何查询时把B表的备注变成字符串

如下
  Aid Aname Abeizhu beizhu
  1 Aname1 Abz1 Bbz1;Bbz2;
  2 Aname2 Abz2 Bbz3;Bbz4;
  3 Aname3 Abz3 Bbz5;Bbz6;



[解决办法]

SQL code

create table 表A(Aid int, Aname varchar(10), Abeizhu varchar(5))insert into 表Aselect 1, 'Aname1', 'Abz1' union allselect 2, 'aname2', 'Abz2' union allselect 3, 'Aname3', 'Abz3'create table 表B(Bid int, Bname varchar(10), Bbeizhu varchar(5), Aid int)  insert into 表Bselect 1, 'Bname1', 'Bbz1', 1 union allselect 2, 'Bname2', 'Bbz2', 1 union allselect 3, 'Bname3', 'Bbz3', 2 union allselect 4, 'Bname1', 'Bbz4', 2 union allselect 5, 'Bname2', 'Bbz5', 3 union allselect 6, 'Bname3', 'Bbz6', 3select a.Aid,a.Aname,a.Abeizhu,b.beizhufrom 表A ainner join (select Aid,cast((select Bbeizhu+';' from 表B b1 where b1.Aid=b0.Aid for xml path('')) as varchar) beizhufrom 表B b0group by Aid) bon a.Aid=b.AidAid         Aname      Abeizhu beizhu----------- ---------- ------- ------------------------------1           Aname1     Abz1    Bbz1;Bbz2;2           aname2     Abz2    Bbz3;Bbz4;3           Aname3     Abz3    Bbz5;Bbz6;(3 row(s) affected)