Oracle 18c

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

create table items (
item_id number,
item varchar2(12),
assembly_id number,
in_assembly_id number
);
BEGIN
insert into items values ( 1 , 'componentA' , -1 , -1 );
insert into items values ( 2 , 'componentA' , -1 , 50 );
insert into items values ( 3 , 'componentB' , -1 , 50 );
insert into items values ( 4 , 'modulAB' , 50 , -1 );
insert into items values ( 5 , 'componentA' , -1 , 100 );
insert into items values ( 6 , 'componentC' , -1 , 100);
insert into items values ( 7 , 'modulAC' , 100 , -1 );
insert into items values ( 7 , 'modulAC' , 100 , 500 );
insert into items values ( 8 , 'componentD' , -1 , 200 );
insert into items values ( 9 , 'componentE' , -1 , 200 );
insert into items values ( 10 , 'modulDE' , 200 , 500 );
insert into items values ( 11 , 'modulACDE' , 500 , -1 );
insert into items values ( 12 , 'componentF' , -1 , -1 );
insert into items values ( 13 , 'componentG' , -1 , -1 );
END;
/
1 rows affected

SELECT CONNECT_BY_ROOT( item_id ) AS root_item_id,
       CONNECT_BY_ROOT( item ) AS root_item,
       item_id,
       item,
       assembly_id,
       SYS_CONNECT_BY_PATH( item_id, ',' ) AS path_item_id,
       SYS_CONNECT_BY_PATH( item, ',' ) AS path_item
FROM   items
WHERE  assembly_id <> -1
START WITH
       item = 'componentA'
AND    ( assembly_id, in_assembly_id ) NOT IN ((-1,-1))
CONNECT BY NOCYCLE
       PRIOR in_assembly_id = assembly_id
ORDER SIBLINGS BY item, item_id;


ROOT_ITEM_ID
ROOT_ITEM
ITEM_ID
ITEM
ASSEMBLY_ID
PATH_ITEM_ID
PATH_ITEM


2
componentA
4
modulAB
50
,2,4
,componentA,modulAB

2
componentA
7
modulAC
100
,2,4,5,7
,componentA,modulAB,componentA,modulAC

2
componentA
7
modulAC
100
,2,4,6,7
,componentA,modulAB,componentC,modulAC

2
componentA
10
modulDE
200
,2,4,8,10
,componentA,modulAB,componentD,modulDE

2
componentA
10
modulDE
200
,2,4,9,10
,componentA,modulAB,componentE,modulDE

5
componentA
7
modulAC
100
,5,7
,componentA,modulAC

5
componentA
10
modulDE
200
,5,7,8,10
,componentA,modulAC,componentD,modulDE

5
componentA
10
modulDE
200
,5,7,9,10
,componentA,modulAC,componentE,modulDE

5
componentA
7
modulAC
100
,5,7
,componentA,modulAC

5
componentA
11
modulACDE
500
,5,7,11
,componentA,modulAC,modulACDE

ROOT_ITEM_ID	ROOT_ITEM	ITEM_ID	ITEM	ASSEMBLY_ID	PATH_ITEM_ID	PATH_ITEM
2	componentA	4	modulAB	50	,2,4	,componentA,modulAB
2	componentA	7	modulAC	100	,2,4,5,7	,componentA,modulAB,componentA,modulAC
2	componentA	7	modulAC	100	,2,4,6,7	,componentA,modulAB,componentC,modulAC
2	componentA	10	modulDE	200	,2,4,8,10	,componentA,modulAB,componentD,modulDE
2	componentA	10	modulDE	200	,2,4,9,10	,componentA,modulAB,componentE,modulDE
5	componentA	7	modulAC	100	,5,7	,componentA,modulAC
5	componentA	10	modulDE	200	,5,7,8,10	,componentA,modulAC,componentD,modulDE
5	componentA	10	modulDE	200	,5,7,9,10	,componentA,modulAC,componentE,modulDE
5	componentA	7	modulAC	100	,5,7	,componentA,modulAC
5	componentA	11	modulACDE	500	,5,7,11	,componentA,modulAC,modulACDE