Create a view of a table with a column that has multiple values

I have a table (Table1) like the following:

So Col2 can contain multiple values comma separated, I have another table (Table2) that contains this:

I need to create a view that joins the two tables (Table1 and Table2) and returns something like this:

Is that possible? (I'm on Oracle DB if that helps.)

It's a violation of first normal form to have a list in a column value like that. It causes a lot of difficulties in a relational database, like the one you are encountering now.

However, you can get what you want by using the LIKE operator to find colA values that are substrings of the Col2 column. Add delimiters before and after to catch the first and last ones. Then aggregate back up to a single list using LISTAGG.

SELECT table1.col1,
       LISTAGG(table2.colB,',') WITHIN GROUP (ORDER BY table2.colB) value_list
  FROM table1,
       table2
 WHERE ','||table1.col2||',' LIKE '%,'||table2.colA||',%'
 GROUP BY table1.col1

This will not perform well on large volumes, because without an equijoin it's going to use nested loops, and you can't use an index on a LIKE predicate with % at the beginning. The combination of nested loops + FTS is not pleasant with large volumes of data. Therefore, if this is your situation, you will need to fix the 1NF problem by transforming table1 into normal relational format, and then join it to table2 with an equijoin, which will enable it to use a hash join instead. So:

SELECT table1.col1,
       LISTAGG(table2.colB,',') WITHIN GROUP (ORDER BY table2.colB) value_list
  FROM (SELECT t.col1,
               SUBSTR(t.col2,INSTR(t.col2,',',1,seq)+1,INSTR(t.col2,',',1,seq+1)-(INSTR(t.col2,',',1,seq)+1)) col2_piece
          FROM (SELECT col1,
                       ','||col2||',' col2
                  FROM table1) t,
               (SELECT ROWNUM seq FROM dual CONNECT BY LEVEL < 10) x) table1,
       table2
 WHERE table1.col2_piece IS NOT NULL
   AND table1.col2_piece = table2.colA
 GROUP BY table1.col1

If you want the values in the same order in the list as the terms then you can use:

SELECT t1.col1,
       LISTAGG(t2.colb, ',') WITHIN GROUP (
         ORDER BY INSTR(','||t1.col2||',', ','||t2.colA||',')
       ) AS value2
FROM   table1 t1
       INNER JOIN table2 t2
       ON INSTR(','||t1.col2||',', ','||t2.colA||',') > 0
GROUP BY 
       t1.col1

Which, for the sample data:

CREATE TABLE Table1 (Col1, Col2) AS
SELECT 'First',  'Code1,Code2,Code3' FROM DUAL UNION ALL
SELECT 'Second', 'Code2' FROM DUAL;

CREATE TABLE Table2 (ColA, ColB) AS
SELECT 'Code1', 'XXXX' FROM DUAL UNION ALL
SELECT 'Code2', 'ZZZZ' FROM DUAL UNION ALL
SELECT 'Code3', 'YYYY' FROM DUAL;

Outputs:

fiddle