问题描述
解决方案
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
解决方案二:
你还是这个存储过程吧,用游标遍历拼接出后两个字段,要么就查出竖表,通过代码组织成你的格式
时间: 2024-08-25 06:50:55