SQL Server 的一对多关系查询

Question

A表和B表是一对多关系，要如何写sql语句实现图中的效果。

Answer 1

SQLServer如下

 CREATE TABLE A(
  id int,
  name varchar(20)
);
CREATE TABLE B(
  bid int,
  name varchar(20),
  pid int
);
insert into A(id,name) VALUES(1,'张三');
insert into A(id,name) VALUES(2,'李四');
insert into B(bid,name,PID) VALUES(1,'王五',1);
insert into B(bid,name,PID) VALUES(2,'张飞',1);
insert into B(bid,name,PID) VALUES(3,'钱六',2);

select a.*,r.data from a left join(
SELECT pid, data=STUFF((SELECT ','+name FROM b t WHERE pid=t1.pid FOR XML PATH('')), 1, 1, '')
FROM b t1
GROUP BY pid
) r on a.id=r.pid