oracle 多行合并为一行。

朋友问的小sql，做个记录。

create table A(
ID varchar2(20),
aaa varchar2(20),
bbb varchar2(20),
ccc varchar2(20),
ddd varchar2(20),
eee varchar2(20)
);
create table B(
ID1 varchar2(20),
dm varchar2(20),
zt varchar2(20)
);

 

 

 

create table C(
ID1 varchar2(20)
);

 

 

insert into C values('111');
insert into B values('111','010','true');
insert into B values('111','015','true');
insert into B values('111','020','true');
insert into B values('111','025','true');
insert into B values('111','030','true');

 

正常插入a表sql 

insert into A select C.ID1,
(case when B.dm='010' then B.zt else '' END) aaa,
(case when B.dm='015' then B.zt else '' END) bbb,
(case when B.dm='020' then B.zt else '' END) ccc,
(case when B.dm='025' then B.zt else '' END) ddd,
(case when B.dm='030' then B.zt else '' END) fff

from C left join B on C.ID1=B.ID1
GROUP BY c.id1,b.dm

 

 要求是插入成一条数据。

SELECT b.id1,wm_concat(case when B.dm='010' then B.zt else '' END) aaa,wm_concat(case when B.dm='015' then B.zt else '' END) bbb,
wm_concat(case when B.dm='020' then B.zt else '' END) ccc,
wm_concat(case when B.dm='025' then B.zt else '' END) ddd,
wm_concat(case when B.dm='030' then B.zt else '' END) fff from c LEFT JOIN b ON c.id1=b.id1
GROUP BY b.id1