Join two tables in BigQuery, using key-value pairs on one table to create columns in the merged table

user_13346340 0
Viewed 484

I have two tables in BigQuery, but I'm struggling to correctly join the content of one into the other.

The first table is of the form (DataUsers):

userID name lastName
us1 John Anders
us2 Terry Garcia

And the second looks like this (DataInfo):

userID key value
us1 address1 us1FirstHome
us2 address1 us2FirstHome
us1 address2 us1SecondHome
us2 address2 us2SecondHome
us1 address3 us1ThirdHome
us2 address3 us2ThirdHome

Since there's a key-value relationship, I would like to create a column for each one of the keys.

I have tried with a query like the next to join the tables:

SELECT
  i.name,
  i.lastname,
  CASE WHEN u.key = 'address1' THEN u.value END AS address1 ,
  CASE WHEN u.key = 'address2' THEN u.value END AS address2 ,
  CASE WHEN u.key = 'address3' THEN u.value END AS address3 ,
FROM
  `DataInfo` i
INNER JOIN
  `DataUsers` u
ON
  i.userID = u.userID
WHERE
  (u.key="address1"
    OR u.key="address2"
    OR u.key="address3")

However, the result is a table like this:

userID name lastname address1 address2 address3
us1 John Anders us1FirstHome null null
us1 John Anders null us1SecondHome null
us1 John Anders null null us1ThirdHome
us2 Terry Garcia us2FirstHome null null
us2 Terry Garcia null us2SecondHome null
us2 Terry Garcia null null us2ThirdHome

Instead of the expected table which would look like this:

userID name lastname address1 address2 address3
us1 John Anders us1FirstHome us1SecondHome us1ThirdHome
us2 Terry Garcia us2FirstHome us2SecondHome us2ThirdHome
  • What would be the correct way to join the tables, and not getting the null values in BigQuery?
  • Is there a way to automatically create the columns according the keys, so that it is not necessary to hardcode their names?

Thanks a lot in advanced.

google-bigquery sql
2 Answers

Consider below (least verbose) approach

select * from (
  select * from DataUsers 
  left join DataInfo using(UserID)
)
pivot (min(value) for key in ('address1', 'address2', 'address3'))

if applied to sample data in your question - output is

enter image description here

user_52219440

Use aggregation:

SELECT i.name, i.lastname,
       MAX(CASE WHEN u.key = 'address1' THEN u.value END) AS address1 ,
       MAX(CASE WHEN u.key = 'address2' THEN u.value END) AS address2 ,
       MAX(CASE WHEN u.key = 'address3' THEN u.value END) AS address3 ,
FROM `DataInfo` i INNER JOIN
     `DataUsers` u
     ON i.userID = u.userID
WHERE u.key IN ('address1', 'address2', 'address3')
GROUP BY 1, 2;
user_11440350
Related