원자재 조건을 직접 넣긴 했는데, 별도 구분조건으로 변경해주시면 됩니다.

WITH W_BOM AS (
SELECT 'AA' AS parent_item ,'a'  AS child_item FROM dual  UNION ALL
SELECT 'BB' AS parent_item ,'b'  AS child_item FROM dual  UNION ALL
SELECT 'CC' AS parent_item ,'c'  AS child_item FROM dual  UNION ALL
SELECT 'AAA' AS parent_item ,'AA'  AS child_item FROM dual  UNION ALL
SELECT 'AAA' AS parent_item ,'BB'  AS child_item FROM dual  UNION ALL
SELECT 'AAB' AS parent_item ,'CC'  AS child_item FROM dual  UNION ALL
SELECT 'ABCD' AS parent_item ,'AAA'  AS child_item FROM dual
UNION ALL SELECT 'ABCD' AS parent_item ,'AAB'  AS child_item FROM dual
)

SELECT root_item
      ,MAX(p_item1) AS  p_item1
      ,MAX(p_item2) AS  p_item2
      ,MAX(p_item3) AS  p_item3
FROM  (
      SELECT connect_by_root child_item AS root_item
            ,CASE WHEN LEVEL = 1 THEN parent_item END  AS p_item1
            ,CASE WHEN LEVEL = 2 THEN parent_item END  AS p_item2
            ,CASE WHEN LEVEL = 3 THEN parent_item END  AS p_item3
      FROM  w_bom w
      START WITH child_item IN ('a' ,'b' ,'c') --원자재 구분 조건
      CONNECT BY PRIOR w.parent_item = w.child_item
      )
GROUP BY root_item 

댓글 등록 감사합니다.

근데.   확인해 보니,  원자재 하나에  복수의 반제품 이 있을 경우  MAX함수 때문에  상위 1개  밖에 표기가 안되는데 MAX(p_item1)

혹시  다수의   상위 품목 표기 구현은 안되는 건가요?

WITH t AS
(
SELECT 'AA' p_item, 'a' item FROM dual
UNION ALL SELECT 'BB'  , 'b'   FROM dual
UNION ALL SELECT 'CC'  , 'a'   FROM dual
UNION ALL SELECT 'AAA' , 'AA'  FROM dual
UNION ALL SELECT 'AAA' , 'BB'  FROM dual
UNION ALL SELECT 'AAB' , 'CC'  FROM dual
UNION ALL SELECT 'ABCD', 'AAA' FROM dual
UNION ALL SELECT 'ABCD', 'AAB' FROM dual
)
SELECT item
     , REGEXP_SUBSTR(p, '[^-]+', 1, 1) item_1
     , REGEXP_SUBSTR(p, '[^-]+', 1, 2) item_2
     , REGEXP_SUBSTR(p, '[^-]+', 1, 3) item_3
  FROM (SELECT CONNECT_BY_ROOT(item) item
             , SYS_CONNECT_BY_PATH(p_item, '-') p
          FROM t
         WHERE CONNECT_BY_ISLEAF = 1
         START WITH item = 'a'
         CONNECT BY PRIOR p_item = item
           AND LEVEL <= 3
        )
;