在BigQuery中将Long table转换为宽表 [英] Convert Long table to wide table in BigQuery

问题描述

我有一个像这样的BigQuery表:

I have a BigQuery table like this:

必需的输出是:

注意::Extended_property_key列中的键不是固定的，它会不断增加.因此，输出"中的列也将继续添加.

Note : The keys in Extended_property_key column are not fixed, it keeps on adding frequently. Hence the columns in Output will also keep on adding.

我需要构建一个Bigquery，它可以处理输出查询中列的动态添加以及数据透视.

I need to build a Bigquery which can handle dynamic adding of columns in output query along with pivoting.

推荐答案

下面是BigQuery标准SQL

Below is for BigQuery Standard SQL

EXECUTE IMMEDIATE '''
SELECT account_id, ''' || (
  SELECT STRING_AGG(DISTINCT "MAX(IF(Extended_property_key = '" || Extended_property_key || "', Extended_property_value, NULL)) AS " || Extended_property_key)
  FROM `project.dataset.table`
) || '''  
FROM `project.dataset.table`
GROUP BY 1
ORDER BY 1
'''   

如果应用于问题中的样本数据-输出为

If applied to sample data in your question - output is

Row account_id  Key1    Key2    Key3     
1   1           Value1  null    null     
2   7           null    Value2  Value3   

这篇关于在BigQuery中将Long table转换为宽表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！