Bigquery/sql: How Do I Join Two Tables And Use A Column Value As Column Name?

I have these tables: Foods | food_id | title | | 1 | soy milk | | 2 | banana | | 3 | apple | Nutrients | food_id | nutrient_id | amount | | 1 | n1

Solution 1:

If you have a fixed list of nutrients, then you can use join and group by:

select f.food_id, f.title,
       max(casewhen n.nutrient_id = 1then n.amount end) as nutrient_1,
       max(casewhen n.nutrient_id = 2then n.amount end) as nutrient_2,
       max(casewhen n.nutrient_id = 3then n.amount end) as nutrient_3
from foods left join
     nutrients n
     on n.food_id = f.food_id
groupby f.food_id, f.title;

Note: This uses a left join in case your data has foods like Twinkies which have no known nutritional value.

If you don't know the full list of nutrients, then you don't know what columns are in the result set. I would suggest using JSON or arrays to represent the values.

Solution 2:

Use ROW_NUMBER with pivoting logic:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY food_id ORDERBY nutrient_id) rn
    FROM Nutrients
)

SELECT
    f.food_id,
    f.title,
    MAX(CASEWHEN t.rn = 1THEN t.amount END) AS n1,
    MAX(CASEWHEN t.rn = 2THEN t.amount END) AS n2,
    MAX(CASEWHEN t.rn = 3THEN t.amount END) AS n3
FROM Foods f
LEFT JOIN cte
    ON f.food_id = t.food_id
GROUPBY
    f.food_id,
    f.title;

Solution 3:

Below is for BigQuery Standard SQL and assumes that number of nutrients is not fixed per food so pivot'ing approach will not be simple and rather answering below question :

how do I put nutrient_id into ... a Struct key?

#standardSQL
SELECT*FROM `project.dataset.Foods` 
LEFTJOIN (
  SELECT food_id, ARRAY_AGG(STRUCT(nutrient_id, amount)) nutrients_facts
  FROM `project.dataset.Nutrients`
  GROUPBY food_id
)
USING(food_id)  

If to apply above to sample data from your question - result is

Solution 4:

Try this

Select food_id, title, 

  max( casewhen nutrient_id =
   'n1' then 
   amount end) as n1, 
   max( casewhen nutrient_id =
   'n2' then 
   amount end) as n2, 
  max( casewhen nutrient_id =
   'n3' then 
   amount end) as n3
  from table1 t1 join
   Table2 t2
 on t1.food_id=t2.food_id
 Groupby food_id, title