Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table order_item_xm (oix_rowid varchar2(10),
                            par_rowid varchar2(10),
                            type      varchar2(30),
                            name      varchar2(30) );
create table order_item (oi_rowid       varchar2(10) );
insert into order_item_xm values ('3-AUXHJ', '3-BHUYI', 'ATS', '2-ATS0J');
1 rows affected

insert into order_item_xm values ('3-AUXHK', '3-BHUYI', 'CART', 'a4beecj02kl');
1 rows affected

insert into order_item_xm values ('3-AUXBW', '3-AKTKZ', 'ATS', 'SA-92');
1 rows affected

insert into order_item_xm values ('3-AUXCY', '3-AKTKZ', 'CART', 'b3kl-hg7tcrd');
1 rows affected

insert into order_item_xm values ('3-AUXMN', '3-AKTKZ', 'CART', 'n9-vgnj78lsd');
1 rows affected

insert into order_item_xm values ('3-CKITY', '3-KB8UI', 'ATS', '2-ATS8U');
1 rows affected

insert into order_item_xm values ('3-CMITY', '3-KB8UI', 'HES', 'HES09');
1 rows affected

insert into order_item_xm values ('3-CZUIO', '3-KB8UI', 'HES', 'HES04');
1 rows affected

insert into order_item_xm values ('3-CZAST', '3-KB8UI', 'ATS', '2-ATS6R');
1 rows affected

insert into order_item(oi_rowid) values ('3-BHUYI');
1 rows affected

insert into order_item(oi_rowid) values ('3-AKTKZ');
1 rows affected

insert into order_item(oi_rowid) values ('3-KB8UI');
1 rows affected

SELECT a.oi_rowid,
       b.type,
       CASE b.type
         WHEN 'CART' THEN
           listagg(b.name, ',') WITHIN GROUP (ORDER BY b.name)
         ELSE
           'No cart id found'
       END cart_id
       FROM order_item a
            INNER JOIN order_item_xm b
                       ON a.oi_rowid = b.par_rowid
       GROUP BY a.oi_rowid,
                b.type;


OI_ROWID
TYPE
CART_ID


3-AKTKZ
ATS
No cart id found

3-AKTKZ
CART
b3kl-hg7tcrd,n9-vgnj78lsd

3-BHUYI
ATS
No cart id found

3-BHUYI
CART
a4beecj02kl

3-KB8UI
ATS
No cart id found

3-KB8UI
HES
No cart id found

OI_ROWID	TYPE	CART_ID
3-AKTKZ	ATS	No cart id found
3-AKTKZ	CART	b3kl-hg7tcrd,n9-vgnj78lsd
3-BHUYI	ATS	No cart id found
3-BHUYI	CART	a4beecj02kl
3-KB8UI	ATS	No cart id found
3-KB8UI	HES	No cart id found