2021 年更新:
A new PIVOT https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#pivot_operatorBigQuery 中引入了运算符。
在使用 PIVOT 将销售额和季度轮换为 Q1、Q2、Q3、Q4 列之前:
product |
sales |
quarter |
Kale |
51 |
Q1 |
Kale |
23 |
Q2 |
Kale |
45 |
Q3 |
Kale |
3 |
Q4 |
Apple |
77 |
Q1 |
Apple |
0 |
Q2 |
Apple |
25 |
Q3 |
Apple |
2 |
Q4 |
使用 PIVOT 将销售额和季度轮换为 Q1、Q2、Q3、Q4 列后:
product |
Q1 |
Q2 |
Q3 |
Q4 |
Apple |
77 |
0 |
25 |
2 |
Kale |
51 |
23 |
45 |
3 |
Query:
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 product, sales, quarter FROM Produce)
PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))
要动态构建列列表,请使用execute immediate
:
execute immediate (
select '''
select *
from (select product, sales, quarter from Produce)
pivot(sum(sales) for quarter in ("''' || string_agg(distinct quarter, '", "' order by quarter) || '''"))
'''
from Produce
);