1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
|
# name: test/sql/pivot/pivot_bigquery.test
# description: Tests from the bigquery docs
# group: [pivot]
# https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#pivot_operator
# https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unpivot_operator
# pivot
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE Produce AS
SELECT 'Kale' as product, 51 as sales, 'Q1' as quarter, 2020 as year UNION ALL
SELECT 'Kale', 23, 'Q2', 2020 UNION ALL
SELECT 'Kale', 45, 'Q3', 2020 UNION ALL
SELECT 'Kale', 3, 'Q4', 2020 UNION ALL
SELECT 'Kale', 70, 'Q1', 2021 UNION ALL
SELECT 'Kale', 85, 'Q2', 2021 UNION ALL
SELECT 'Apple', 77, 'Q1', 2020 UNION ALL
SELECT 'Apple', 0, 'Q2', 2020 UNION ALL
SELECT 'Apple', 1, 'Q1', 2021
query IIIIII
SELECT * FROM Produce
PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))
ORDER BY ALL
----
Apple 2020 77 0 NULL NULL
Apple 2021 1 NULL NULL NULL
Kale 2020 51 23 45 3
Kale 2021 70 85 NULL NULL
query IIIII
SELECT * FROM
(SELECT product, sales, quarter FROM Produce)
PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))
ORDER BY ALL
----
Apple 78 0 NULL NULL
Kale 121 108 45 3
query IIII
SELECT * FROM
(SELECT product, sales, quarter FROM Produce)
PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3'))
ORDER BY ALL
----
Apple 78 0 NULL
Kale 121 108 45
query III
SELECT * FROM
(SELECT sales, quarter FROM Produce)
PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3'))
ORDER BY ALL
----
199 108 45
query IIIII
SELECT * FROM
(SELECT product, sales, quarter FROM Produce)
PIVOT(SUM(sales) total_sales, COUNT(*) num_records FOR quarter IN ('Q1', 'Q2'))
ORDER BY ALL
----
Apple 78 2 0 1
Kale 121 2 108 2
statement ok
CREATE OR REPLACE TABLE Produce AS
SELECT 'Kale' as product, 51 as Q1, 23 as Q2, 45 as Q3, 3 as Q4 UNION ALL
SELECT 'Apple', 77, 0, 25, 2
query III
SELECT * FROM Produce
UNPIVOT(sales FOR quarter IN (Q1, Q2, Q3, Q4))
ORDER BY ALL
----
Apple Q1 77
Apple Q2 0
Apple Q3 25
Apple Q4 2
Kale Q1 51
Kale Q2 23
Kale Q3 45
Kale Q4 3
query IIII
SELECT product, first_half_sales, second_half_sales, semesters FROM Produce
UNPIVOT(
(first_half_sales, second_half_sales)
FOR semesters
IN ((Q1, Q2) AS 'semester_1', (Q3, Q4) AS 'semester_2'))
----
Kale 51 23 semester_1
Kale 45 3 semester_2
Apple 77 0 semester_1
Apple 25 2 semester_2
|
