CODEWITHSUNDEEP
sqljoingoogle-bigquery
MartinD
MartinD

Reputation: 67

How do I replace values in multiple columns using a lookup table that contains all the mappings?

I've got a table which contains 4 columns of product codes (plus other columns). I have another table that contains a column of all_codesand another column with the descriptions of the codes.

How do I join (or replace) the multiple columns of codes in the first table to the descriptions in the second table?

I've managed to use the replace function to get the description for one of my columns by joining ontbl1.code1 = tbl2.all_codes, but can't get it to extend to work for

tbl1.code2 = tbl2.all_codes, tbl1.code3 = tbl2.all_codes etc etc.

Table 1

    OrderNo  |  Style_code |   Color_code |   Country_code
-------------|-------------|--------------|---------------
     21540   |    abc      |     kdx      |      plo
     21541   |    gcs      |     kdy      |      pla
     21542   |    wer      |     kdz      |      plh
     21543   |    abc      |     kdx      |      pld
     21544   |    gcs      |     kdy      |      plo

Table 2

 all_codes   |  description
 ------------|-----------------  
    abc      |     plain
    gcs      |     vintage
    wer      |     modern
    kdx      |     white
    kdy      |     gray
    kdz      |     black
    plo      |     USA
    pla      |     Mexico
    plh      |     Canada
    pld      |     Brazil

Desired output

    OrderNo  |  Style_desc |   Color_desc |   Country_desc
-------------|-------------|--------------|---------------
     21540   |  plain      |  white       |      USA
     21541   |  vintage    |  gray        |      Mexico
     21542   |  modern     |  black       |      Canada
     21543   |  plain      |  white       |      Brazil
     21544   |  vintage    |  gray        |      USA

Can anyone help me figure the best way to do this?

Upvotes: 2

Views: 988

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Can anyone help me figure the best way to do this?

Below option is for BigQuery Standard SQL. If not the best - definitely good one to try. Assumption is the all_codes table is not that big so array of those codes is manageable ... 

#standardSQL
CREATE TEMP FUNCTION code_mapping(code STRING, all_codes ARRAY<STRUCT<all_codes STRING, description STRING>>) AS ((
  SELECT description FROM UNNEST(all_codes) WHERE code = all_codes
));
SELECT OrderNo, 
  code_mapping(Style_code, codes) Style_desc,
  code_mapping(Color_code, codes) Color_desc,
  code_mapping(Country_code, codes) Country_desc
FROM `project.dataset.table1`
CROSS JOIN (SELECT ARRAY_AGG(t) codes FROM `project.dataset.table2` t)

You can test, play with above using sample data from your question as in below example 

#standardSQL
CREATE TEMP FUNCTION code_mapping(code STRING, all_codes ARRAY<STRUCT<all_codes STRING, description STRING>>) AS ((
  SELECT description FROM UNNEST(all_codes) WHERE code = all_codes
));
WITH `project.dataset.table1` AS (
  SELECT 21540 OrderNo, 'abc' Style_code, 'kdx' Color_code, 'plo' Country_code UNION ALL
  SELECT 21541, 'gcs', 'kdy', 'pla' UNION ALL
  SELECT 21542, 'wer', 'kdz', 'plh' UNION ALL
  SELECT 21543, 'abc', 'kdx', 'pld' UNION ALL
  SELECT 21544, 'gcs', 'kdy', 'plo' 
), `project.dataset.table2` AS (
  SELECT 'abc' all_codes, 'plain' description UNION ALL
  SELECT 'gcs', 'vintage' UNION ALL
  SELECT 'wer', 'modern' UNION ALL
  SELECT 'kdx', 'white' UNION ALL
  SELECT 'kdy', 'gray' UNION ALL
  SELECT 'kdz', 'black' UNION ALL
  SELECT 'plo', 'USA' UNION ALL
  SELECT 'pla', 'Mexico' UNION ALL
  SELECT 'plh', 'Canada' UNION ALL
  SELECT 'pld', 'Brazil' 
)
SELECT OrderNo, 
  code_mapping(Style_code, codes) Style_desc,
  code_mapping(Color_code, codes) Color_desc,
  code_mapping(Country_code, codes) Country_desc
FROM `project.dataset.table1`
CROSS JOIN (SELECT ARRAY_AGG(t) codes FROM `project.dataset.table2` t)

with result 

Row OrderNo Style_desc  Color_desc  Country_desc     
1   21540   plain       white       USA  
2   21541   vintage     gray        Mexico   
3   21542   modern      black       Canada   
4   21543   plain       white       Brazil   
5   21544   vintage     gray        USA

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You want multiple joins:

select t1.orderNo, acs.description as style_desc,
       acc.description as color_desc,
       acco.description as country_desc
from table1 t1 left join
     all_codes acs
     on t1.style_code = acs.code left join
     all_codes acc
     on t1.color_code = acc.code left join
     all_codes acco
     on t1.country_code = acco.code ;

This version uses left join, just in case any values do not match the reference table.

Upvotes: 1

Related Questions