WITH A_BOM1 AS (

SELECT 'BOM1' AS BOMID , 'A' AS TO_ITEM , 'a' as FROM_ITEM from dual

UNION ALL

SELECT 'BOM1' AS BOMID , 'A' AS TO_ITEM , 'b' as from_item from dual

UNION ALL

SELECT 'BOM1' AS BOMID , 'A' AS TO_ITEM , 'c' as from_item from dual

UNION ALL

SELECT 'BOM2' AS BOMID , 'A' AS TO_ITEM , 'a' as from_item from dual

UNION ALL

SELECT 'BOM2' AS BOMID , 'A' AS TO_ITEM , 'b' as from_item from dual

UNION ALL

SELECT 'BOM2' AS BOMID , 'A' AS TO_ITEM , 'c' as from_item from dual

UNION ALL

SELECT 'BOM3' AS BOMID , 'A' AS TO_ITEM , 'a' as from_item from dual

UNION ALL

SELECT 'BOM3' AS BOMID , 'A' AS TO_ITEM , 'c' as from_item from dual

UNION ALL

SELECT 'BOM3' AS BOMID , 'A' AS TO_ITEM , 'd' as from_item from dual

)

SELECT

*

FROM (

SELECT

BOMID, A1

,RANK() OVER (PARTITION BY A1 ORDER BY ROWNUM) A2

FROM (

SELECT

BOMID,

LISTAGG(ITEM_SEQ,',') WITHIN GROUP (ORDER BY BOMID,ITEM_SEQ) A1

FROM (

SELECT BOMID ,

TO_ITEM||FROM_ITEM ,

DENSE_RANK () OVER(ORDER BY TO_ITEM||FROM_ITEM ) AS ITEM_SEQ

FROM A_BOM

)

GROUP BY BOMID

)

)

WHERE A2 > 1