Replace values to different values

Question

Please help me. I need to replace table1's code1,code2,code3 values to table2 description.

Table 1

ID  CODE1   CODE2   CODE3                                   
--------------------------                  
222 4wta    5qer    2qrst                   
223 5qer    4rstu   4tws
224 4tws    2thua   1thur
225 4tws    5qer    3wrst

Table 2

code    description
-------------------
4wta    Good
5qer    medium
2qrst   Average
1thur   Mild
3wrst   Progress
2thua   Success
4rstu   Poor
4tws    Low

After Replace

 ID CODE1   CODE2   CODE3  
-----------------------------
222 Good    medium  Average

Is this what you're looking for? https://stackoverflow.com/q/2446764/2422776 — Mureinik, Mar 27 '20 at 13:52

score 0 · Answer 1 · answered Mar 27 '20 at 14:10

You can join table2 to table1 for each column (this will require 3 joins):

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

Or you could unpivot table1 from columns to rows then perform a single join to table2 and then pivot the table back from rows to columns:

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
  )
)

Both of them output:

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

db<>fiddle here

Thanks a lot for your prompt reply. But when I run the code I got for every code1,code2,and code3 'null'. I used pivot .why? — Anu, Mar 27 '20 at 15:44
@Anu Without your data & code I cannot tell what has happened. Please create a [MRE] that replicates the issue; you could use [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2) (or there are many other equivalent services) where you could write a simple example of your issue that we can see and help you to debug. — MT0, Mar 27 '20 at 16:22
I used db<> fiddle for the first time and tried it again. I got it correctly. Thank you so much MTO. I really appreciate your time and help. — Anu, Mar 27 '20 at 19:31
But when I try it in SQL developer, It gives errors. Please can you explain (ORA-00936: missing expression) — Anu, Mar 27 '20 at 19:47

Replace values to different values

1 Answers1