-1

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    |      |    |    |

The column titles should be represented by whatever found in nutrient_id column, not by actual strings "n1", "n2" etc. For example, if the nutrient id is "some-nutrient-123", then I want to see a column in results with the title "some-nutrient-123".

Struct would also work.

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

stkvtflw
  • 12,092
  • 26
  • 78
  • 155
  • This may help you https://stackoverflow.com/questions/39394111/dynamically-creating-columns-from-row-data-using-select-in-bigquery – zealous Apr 21 '20 at 06:03

1 Answers1

0

You are looking for a query like:

with t1 as (
SELECT
  food_id as id_new,
  SUM(CASE WHEN nutrient_id = 'n1' THEN amount END) AS n1,
  SUM(CASE WHEN nutrient_id = 'n2' THEN amount END) AS n2,
  SUM(CASE WHEN nutrient_id = 'n3' THEN amount END) AS n3
FROM `<Nutrients>` 
GROUP BY id_new
ORDER BY id_new
)
SELECT * FROM `<Foods>` as t2 JOIN t1 
on t2.id = t1.id_new

Now, I understand that you do not want to hardcode the nutrient names. I am not certain if that is possible in BigQuery as of now. My workaround would be to run for example the query

SELECT 
CONCAT(
'SUM(CASE WHEN nutrient_id = "' , nutrient_id , '" THEN amount END) AS ', nutrient_id
    )
FROM (
  SELECT nutrient_id FROM <Nutrients> GROUP BY nutrient_id ORDER BY nutrient_id
)

following what is done in zealous link. This will give you all the cases to consider.

aemon4
  • 1,037
  • 6
  • 11