BigQuery中的新PIVOT函数

Question

今天BigQuery发布了一个名为PIVOT的新酷函数。

Se下面的工作原理：

with Produce AS (
SELECT 'Kale' as product, 51 as sales, 'Q1' as quarter UNION ALL
SELECT 'Kale', 23, 'Q2' UNION ALL
SELECT 'Kale', 45, 'Q3' UNION ALL
SELECT 'Kale', 3, 'Q4' UNION ALL
SELECT 'Apple', 77, 'Q1' UNION ALL
SELECT 'Apple', 0, 'Q2' UNION ALL
SELECT 'Apple', 25, 'Q3' UNION ALL
SELECT 'Apple', 2, 'Q4')
SELECT * FROM
(SELECT * FROM Produce)
PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

+---------+----+----+----+----+
| product | Q1 | Q2 | Q3 | Q4 |
+---------+----+----+----+----+
| Apple   | 77 | 0  | 25 | 2  |
| Kale    | 51 | 23 | 45 | 3  |
+---------+----+----+----+----+

我的问题是，在现实世界中，我们并不提前知道季度价值。

我试着用做得更动态

SELECT * FROM
(SELECT * FROM Produce)
PIVOT(SUM(sales) FOR quarter in (select distinct quarter from Produce))

没有成功。有关于如何处理的线索吗？

这是reddit交叉发布的链接

Answer 1

使用以下内容-它动态构建数据透视列

execute immediate (             
select '''select * from (select * from `project.dataset.Produce`)
pivot(sum(sales) for quarter in ("''' ||  string_agg(quarter, '", "')  || '''"))
'''
from (select distinct quarter from `project.dataset.Produce` order by quarter) 
);