BigQuery/SQL:如何使用列值作为列名? [英] BigQuery/SQL: How do I use a column value as column name?

问题描述

我有这些表:

食物

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

营养素

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

我需要这个:

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

列标题应该用在营养ID 列中找到的任何内容表示，而不是用实际的字符串"n1"，"n2"等表示.例如，如果营养ID是"some-养分123"，那么我想在结果中看到标题为"some-nutrition-123"的列.

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 也可以.

我知道所有联接，但是无法解决这个问题……如何将营养ID 的值放入列标题或结构键?

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?

推荐答案

您正在寻找类似这样的查询:

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 

现在，我了解到您不想对营养素名称进行硬编码.我不确定目前BigQuery是否可行.我的解决方法是运行例如查询

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.

这篇关于BigQuery/SQL:如何使用列值作为列名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！