Find all values in column B that correspond to particular values in column A ORACLE SQL

The listagg function should do the trick:

SELECT   b, LISTAGG(a, ',') WITHIN GROUP (ORDER BY a)
FROM     mytable
GROUP BY b

Principally, the data should be grouped by the column B, and the concatenation should be applied to the column A.

1. If version of DB is 11gR2+, then the LISTAGG() function might be applied :

   SELECT B, 
          LISTAGG(A, ',') WITHIN GROUP 
          ( ORDER BY TO_NUMBER( REGEXP_REPLACE(A,'[^0-9]')) ) AS "Concatenated String"
     FROM tab
    GROUP BY B
    ORDER BY B
   

2. If version of DB is 11gR2-, then the XMLAGG(XMLELEMENT()) function combination might be applied :

   SELECT B,
          RTRIM(DBMS_XMLGEN.CONVERT(
                  XMLAGG( 
                         XMLELEMENT(e,A||',')
                        ).EXTRACT('//text()').GETCLOBVAL() ,1),
                 ',') AS "Concatenated String"
     FROM tab
    GROUP BY B
    ORDER BY B
   

• Special Case If ORA-01489: result of string concatenation is too long , which raises whenever the concatenated string within the first argument exceeds the length of 4000 characters, occured :

  The above 2nd case is applied. If the version of DB is 12gR2+, then the 1st case might be used as

  SELECT B, 
         LISTAGG(A, ',' ON OVERFLOW TRUNCATE 'THE REST IS TRUNCATED' WITHOUT COUNT ) 
         WITHIN GROUP 
         ( ORDER BY TO_NUMBER( REGEXP_REPLACE(A,'[^0-9]')) ) AS "Concatenated String"
    FROM tab
   GROUP BY B
   ORDER BY B
  

  in order to truncate the trailing part without getting the error ORA-01489 is raised.

Demo