WITH a AS
(
SELECT 'A' no, 1 tot, 1 rank_1, 0 rank_2, 0 rank_3 FROM dual
UNION ALL SELECT 'A1' , 2, 0, 1, 1 FROM dual
UNION ALL SELECT 'A11', 3, 1, 1, 1 FROM dual
UNION ALL SELECT 'B'  , 0, 0, 0, 0 FROM dual
)
, b AS
(
SELECT 'A' no, '' prnt_no FROM dual
UNION ALL SELECT 'A1' , 'A'  FROM dual
UNION ALL SELECT 'A11', 'A1' FROM dual
UNION ALL SELECT 'B'  , ''   FROM dual
)
SELECT no
     , TO_NUMBER(REGEXP_SUBSTR(s, '[^,]+', 1, 1)) tot
     , TO_NUMBER(REGEXP_SUBSTR(s, '[^,]+', 1, 2)) rank_1
     , TO_NUMBER(REGEXP_SUBSTR(s, '[^,]+', 1, 3)) rank_2
     , TO_NUMBER(REGEXP_SUBSTR(s, '[^,]+', 1, 4)) rank_3
  FROM (SELECT no
             , (SELECT SUM(a.tot)
                       ||','|| SUM(rank_1)
                       ||','|| SUM(rank_2)
                       ||','|| SUM(rank_3)
                  FROM a, b
                 WHERE a.no = b.no
                 START WITH b.no = c.no
                 CONNECT BY PRIOR b.no = b.prnt_no
                ) s
          FROM b c
         START WITH prnt_no IS NULL
         CONNECT BY PRIOR no = prnt_no
        )
;

마농님 정말 감사합니다.

많이 배우고 있습니다. ^^