BigQuery/SQL: How do I join two tables and use a column value as column name?

Question

I have these tables:

Foods

| food_id | title    |
| 1       | soy milk |
| 2       | banana   |
| 3       | apple    |

Nutrients

| food_id | nutrient_id | amount |
| 1       | n1          | 0.05   |
| 1       | n2          | 2      |
| 1       | n3          | 34     |
...

I need this:

| food_id | title    | n1   | n2 | n3 |
| 1       | soy milk | 0.05 | 2  | 34 |
| 2       | banana   |      |    |    |  
| 3       | apple    |      |    |    |

Struct would also work.

I know all the joins, but can't wrap my head around this... how do I put nutrient_id into a column title or a Struct key?

Answer 1

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

select f.food_id, f.title,
       max(case when n.nutrient_id = 1 then n.amount end) as nutrient_1,
       max(case when n.nutrient_id = 2 then n.amount end) as nutrient_2,
       max(case when n.nutrient_id = 3 then n.amount end) as nutrient_3
from foods left join
     nutrients n
     on n.food_id = f.food_id
group by 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.

Answer 2

Use ROW_NUMBER with pivoting logic:

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

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

Answer 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` 
LEFT JOIN (
  SELECT food_id, ARRAY_AGG(STRUCT(nutrient_id, amount)) nutrients_facts
  FROM `project.dataset.Nutrients`
  GROUP BY food_id
)
USING(food_id)  

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

Answer 4

Try this

  Select food_id, title, 

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