Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (states, Firstname, LastName) AS
SELECT 'IL,AL', 'John', 'Smith' FROM DUAL UNION ALL
SELECT 'AK,TX', 'Mary', 'Peter' FROM DUAL;
2 rows affected

CREATE TABLE states (code, name) AS
SELECT 'IL', 'Illinois' FROM DUAL UNION ALL
SELECT 'AL', 'Alabama'  FROM DUAL UNION ALL
SELECT 'AK', 'Alaska'   FROM DUAL UNION ALL
SELECT 'TX', 'Texas'    FROM DUAL;
4 rows affected

SELECT ( SELECT LISTAGG(name, ',') WITHIN GROUP (
                  ORDER BY INSTR(','||t.states||',', ','||s.code||',')
                )
         FROM   states s
         WHERE  INSTR(','||t.states||',', ','||s.code||',') > 0
       ) AS states,
       firstname,
       lastname
FROM   table_name t;


STATES
FIRSTNAME
LASTNAME


Illinois,Alabama
John
Smith

Alaska,Texas
Mary
Peter