Thanks for the question, Mary.

Asked: January 14, 2019 - 7:18 pm UTC

Last updated: January 18, 2019 - 10:11 am UTC

Version: 11

Viewed 1000+ times

You Asked

Hi Everyone,

I have a challenge to use ORACLE SQL to mimic what Excel does matching 2 tables based on the header and content. Details are follows.

TABLE 1
cid CName Age Height
001 Mary E40 E22
002 Cat E22 E40

TABLE 2
Data CODE MEANING
Age E40 40 years old
Age E22 22 years old
Height E22 5'2
Height E40 5'4

GOAL:
To replace codes in TABLE 1 with corresponding meaning

EXPECTED RESULT:
cid CName Age Height
001 Mary 40 years old 5'2
002 Cat 22 years old 5'4

CURRENT WAY OF DOING - EXCEL:
1. Add a calculated field in TABLE 2 combining Data and Code fields
Dfield CODE MEAING Combine
Age E40 40 years old AgeE40
Age E22 22 years old AgeE22
Height E22 5'2 HeightE22
Height E40 5'4 HeightE40

2. Use match and index to replace code from TABLE 1 with meaning from TABLE 2
1 A B C D E F
2 cid CName Age Height Row # under column Combine Row# under Column Combine
3 001 Mary E40 E22 MATCH(C$1&C2,$D$7:$D$10,0) MATCH(D$1&D2,$D$7:$D$10,0)
4 002 Cat E22 E40 MATCH(C$1&C3,$D$7:$D$10,0) MATCH(D$1&D3,$D$7:$D$10,0)

1 G H
2 Replace Age with Meaning Replace Height with Meaning
3 INDEX($C$7:$C$10,H2,1) INDEX($C$7:$C$10,I2,1)
4 INDEX($C$7:$C$10,H3,1) INDEX($C$7:$C$10,I3,1)

$C$7:$C$10 is the range of data for Meaning from TABLE 2

Your help is truly appreciated!!!

Thank you!!

Mary

with LiveSQL Test Case:

https://livesql.oracle.com/apex/livesql/s/htvyj9lcc8kcf2gltacijy67i

and Chris said...

Here's one way to approach this:

Join the two tables where:

- The AGE column = the code in T2 AND T2 field type is Age
OR
- The HEIGHT column = the code in T2 AND T2 field type is Height

e.g.:

select *
from   table_1 t1
join   table_2 t2
on     ( t1.age = t2.code and t2.dfield = 'Age' ) or 
       ( t1.height = t2.code and t2.dfield = 'Height' );

CID   CNAME   AGE   HEIGHT   DFIELD   CODE   MEANING        
001   Mary    E40   E22      Height   E22    5"2            
001   Mary    E40   E22      Age      E40    40 years old   
002   Cat     E22   E40      Height   E40    5"4            
002   Cat     E22   E40      Age      E22    22 years old

This gives you a row/column in t1 you've joined to t2. To collapse these back into one, pivot the results:

with rws as ( 
  select cid, cname, dfield, meaning
  from   table_1 t1
  join   table_2 t2
  on     ( t1.age = t2.code and t2.dfield = 'Age' ) or 
         ( t1.height = t2.code and t2.dfield = 'Height' ) 
) 
  select * from rws
  pivot ( 
    max ( meaning ) for 
    dfield in ( 
      'Age' age, 'Height' height 
    ) 
  )
  order  by 1;

CID   CNAME   AGE            HEIGHT   
001   Mary    40 years old   5"2      
002   Cat     22 years old   5"4

Here's the create table + inserts:

create table table_1 (
  cid varchar(25), 
  cname varchar(25), 
  age varchar(25), 
  height varchar(25) 
);

create table table_2 (
  dfield varchar(25), 
  code varchar(25), 
  meaning varchar(25) 
);

insert into table_1 values ('001', 'Mary', 'E40', 'E22');
insert into table_1 values ('002', 'Cat', 'E22', 'E40') ;

insert into table_2 values ('Height', 'E22', '5"2');
insert into table_2 values ('Height', 'E40', '5"4') ;

insert into table_2 values ('Age','E40','40 years old') ;
insert into table_2 values ('Age', 'E22', '22 years old') ;

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you very much!!

Mary Yan, January 15, 2019 - 9:51 pm UTC

Hi Chris,

Thank you very much for the reply and this is exactly what I need.
My gratitude is beyond words!

Have a great day!

Mary

January 16, 2019 - 2:58 am UTC

glad we could help

The below approach also should work

Akash Jain, January 17, 2019 - 11:23 am UTC

SELECT
    tab1.cid,
    tab1.cname,
    (
        SELECT
            meaning
        FROM
            table_2 tab2
        WHERE
            tab2.dfield = 'Age'
            AND tab2.code = tab1.age
    ) age,
    (
        SELECT
            meaning
        FROM
            table_2 tab2
        WHERE
            tab2.dfield = 'Height'
            AND tab2.code = tab1.height
    ) height
FROM
    table_1 tab1;

is there any concern with this approach ?

January 18, 2019 - 10:11 am UTC

That works, but you access table2 twice for each row in table1. The join and pivot approach can do this once.

So it'll scale better. Particularly if you start adding more attributes in table2 you want to link to table1.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thank you very much!!

The below approach also should work

More to Explore

SQL

Oracle SQL version of Index and Match in Excel

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thank you very much!!

The below approach also should work

More to Explore

SQL