create table test1
(
aa varchar2(8),
bb number(10),
cc number(10),
dd number(10),
ee number(10)
)
create table test2
(
m_aa varchar2(8),
m_nn varchar2(8),
)
insert into test3(colu1,colu2,colu3,colu4,colu5)
select
test2.m_nn as colu1,
(select sum(bb) from test1 where test1.aa like test2.m_aa ||'%') as colu2,
(select sum(cc) from test1 where test1.aa like test2.m_aa ||'%') as colu3,
(select sum(dd) from test1 where test1.aa like test2.m_aa ||'%') as colu4,
(select sum(ee) from test1 where test1.aa like test2.m_aa ||'%') as colu5
from test2
(
aa varchar2(8),
bb number(10),
cc number(10),
dd number(10),
ee number(10)
)
create table test2
(
m_aa varchar2(8),
m_nn varchar2(8),
)
insert into test3(colu1,colu2,colu3,colu4,colu5)
select
test2.m_nn as colu1,
(select sum(bb) from test1 where test1.aa like test2.m_aa ||'%') as colu2,
(select sum(cc) from test1 where test1.aa like test2.m_aa ||'%') as colu3,
(select sum(dd) from test1 where test1.aa like test2.m_aa ||'%') as colu4,
(select sum(ee) from test1 where test1.aa like test2.m_aa ||'%') as colu5
from test2
select a.m_nn,sum(b.bb) bb,sum(b.cc) cc,sum(b.dd) dd,sum(b.ee) ee
from test2 a,test1 b
where b.aa like a.m_aa||'%'
group by a.m_nn