Oracle 11g Release 2

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

CREATE TABLE table1 ( ID, CODE1, CODE2, CODE3 ) AS
SELECT 222, '4wta', '5qer',  '2qrst' FROM DUAL UNION ALL
SELECT 223, '5qer', '4rstu', '4tws'  FROM DUAL UNION ALL
SELECT 224, '4tws', '2thua', '1thur' FROM DUAL UNION ALL
SELECT 225, '4tws', '5qer',  '3wrst' FROM DUAL;
4 rows affected

CREATE TABLE table2 ( code, description ) AS
SELECT '4wta',    'Good' FROM DUAL UNION ALL
SELECT '5qer',    'medium' FROM DUAL UNION ALL
SELECT '2qrst',   'Average' FROM DUAL UNION ALL
SELECT '1thur',   'Mild' FROM DUAL UNION ALL
SELECT '3wrst',   'Progress' FROM DUAL UNION ALL
SELECT '2thua',   'Success' FROM DUAL UNION ALL
SELECT '4rstu',   'Poor' FROM DUAL UNION ALL
SELECT '4tws',    'Low' FROM DUAL;
8 rows affected

SELECT t1.id,
       t2a.description AS code1,
       t2b.description AS code2,
       t2c.description AS code3
FROM   table1 t1
       INNER JOIN table2 t2a ON t1.code1 = t2a.code
       INNER JOIN table2 t2b ON t1.code2 = t2b.code
       INNER JOIN table2 t2c ON t1.code3 = t2c.code


ID
CODE1
CODE2
CODE3


222
Good
medium
Average

224
Low
Success
Mild

225
Low
medium
Progress

223
medium
Poor
Low


WITH pivoted_values ( id, name, description ) AS (
  SELECT id,
         name,
         t2.description
  FROM   (
           SELECT *
           FROM   table1
           UNPIVOT ( code FOR name IN ( code1, code2, code3 ) )
         ) t1
         INNER JOIN table2 t2 ON t1.code = t2.code
)
SELECT id,
       code1,
       code2,
       code3
FROM   pivoted_values
PIVOT (
  MAX( description )
  FOR name IN (
    'CODE1' AS code1,
    'CODE2' AS code2,
    'CODE3' AS code3
  )
)


ID
CODE1
CODE2
CODE3


222
Good
medium
Average

223
medium
Poor
Low

224
Low
Success
Mild

225
Low
medium
Progress

ID	CODE1	CODE2	CODE3
222	Good	medium	Average
224	Low	Success	Mild
225	Low	medium	Progress
223	medium	Poor	Low