create table t_a (
c1 integer ,
c2 integer,
c3 integer,
sub_id_a integer
)
;
create table t_b (
sub_id_b integer,
part_num varchar(10)
)
;
insert into t_a values(1, 2, 3, 4 ),(1, 2 ,2 ,4 ),(2, 3, 4, 5);
insert into t_b values(4 ,'ef'),(4, 'ed' ),(5, 'eg');
insert into t_b values(5, 'eg');
select * from t_b ;
select a.*,
(select listagg(part_num ,',') from t_b where sub_id_a =sub_id_b)
from t_a a ;
C1 C2 C3 SUB_ID_A 5
-- -- -- -------- -----
1 2 3 4 ef,ed
1 2 2 4 ef,ed
2 3 4 5 eg
;
select a.*,
listagg(part_num ,',')
from t_a a ,t_b
where sub_id_a =sub_id_b
group by a.c1,a.c2,a.c3,a.sub_id_a;
C1 C2 C3 SUB_ID_A 5
-- -- -- -------- -----
1 2 2 4 ef,ed
1 2 3 4 ef,ed
2 3 4 5 eg
with temp as (
select a.*,
part_num
from t_a a ,t_b
where sub_id_a =sub_id_b
)
select c1,c2,c3 ,sub_id_a,
listagg(part_num,',') within group (order by part_num )as part_num
from temp
group by c1,c2,c3 ,sub_id_a
with ur ;
C1 C2 C3 PART_NUM
-- -- -- --------
1 2 2 ef,ed
1 2 3 ef,ed
2 3 4 eg
;