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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
|
# name: test/sql/pivot/pivot_databricks.test
# description: Tests from the databricks docs
# group: [pivot]
# https://docs.databricks.com/sql/language-manual/sql-ref-syntax-qry-select-pivot.html
statement ok
PRAGMA enable_verification
statement ok
CREATE TEMP VIEW sales(year, quarter, region, sales) AS
VALUES (2018, 1, 'east', 100),
(2018, 2, 'east', 20),
(2018, 3, 'east', 40),
(2018, 4, 'east', 40),
(2019, 1, 'east', 120),
(2019, 2, 'east', 110),
(2019, 3, 'east', 80),
(2019, 4, 'east', 60),
(2018, 1, 'west', 105),
(2018, 2, 'west', 25),
(2018, 3, 'west', 45),
(2018, 4, 'west', 45),
(2019, 1, 'west', 125),
(2019, 2, 'west', 115),
(2019, 3, 'west', 85),
(2019, 4, 'west', 65);
query IIIIII rowsort
SELECT year, region, q1, q2, q3, q4
FROM sales
PIVOT (sum(sales)
FOR quarter
IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4));
----
2018 east 100 20 40 40
2018 west 105 25 45 45
2019 east 120 110 80 60
2019 west 125 115 85 65
# multiple matches with aliases
query IIIIIIIII rowsort
SELECT year, q1_east, q1_west, q2_east, q2_west, q3_east, q3_west, q4_east, q4_west
FROM sales
PIVOT (sum(sales)
FOR (quarter, region)
IN ((1, 'east') AS q1_east, (1, 'west') AS q1_west, (2, 'east') AS q2_east, (2, 'west') AS q2_west,
(3, 'east') AS q3_east, (3, 'west') AS q3_west, (4, 'east') AS q4_east, (4, 'west') AS q4_west));
----
2018 100 105 20 25 40 45 40 45
2019 120 125 110 115 80 85 60 65
# inconsistent amount of names
statement error
SELECT year, q1_east, q1_west, q2_east, q2_west, q3_east, q3_west, q4_east, q4_west
FROM sales
PIVOT (sum(sales)
FOR (quarter, region, too_many_names)
IN ((1, 'east') AS q1_east, (1, 'west') AS q1_west, (2, 'east') AS q2_east, (2, 'west') AS q2_west,
(3, 'east') AS q3_east, (3, 'west') AS q3_west, (4, 'east') AS q4_east, (4, 'west') AS q4_west));
----
inconsistent amount of rows
# inconsistent amount of rows in the IN list
statement error
SELECT year, q1_east, q1_west, q2_east, q2_west, q3_east, q3_west, q4_east, q4_west
FROM sales
PIVOT (sum(sales)
FOR (quarter, region)
IN ((1, 'east', 'west') AS q1_east, (1, 'west') AS q1_west, (2, 'east') AS q2_east, (2, 'west') AS q2_west,
(3, 'east') AS q3_east, (3, 'west') AS q3_west, (4, 'east') AS q4_east, (4, 'west') AS q4_west));
----
inconsistent amount of rows
# duplicate values in IN list
statement error
SELECT *
FROM sales
PIVOT (sum(sales)
FOR (quarter, region)
IN ((1, 'east') AS q1_east, (1, 'east') AS q1_east_2));
----
specified multiple times in the IN clause
query IIIII rowsort
SELECT year, q1, q2, q3, q4
FROM (SELECT year, quarter, sales FROM sales) AS s
PIVOT (sum(sales)
FOR quarter
IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4));
----
2018 205 45 85 85
2019 245 225 165 125
# multiple aggregations
query IIIIIIIII rowsort
SELECT year, q1_total, q1_avg, q2_total, q2_avg, q3_total, q3_avg, q4_total, q4_avg
FROM (SELECT year, quarter, sales FROM sales) AS s
PIVOT (sum(sales) AS total, avg(sales) AS avg
FOR quarter
IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4));
----
2018 205 102.5 45 22.5 85 42.5 85 42.5
2019 245 122.5 225 112.5 165 82.5 125 62.5
# multiple aggregations without aliases
query IIIIIIIII rowsort
SELECT *
FROM (SELECT year, quarter, sales FROM sales) AS s
PIVOT (sum(sales), avg(sales)
FOR quarter
IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4));
----
2018 205 102.5 45 22.5 85 42.5 85 42.5
2019 245 122.5 225 112.5 165 82.5 125 62.5
# unpivot
statement ok
CREATE OR REPLACE TEMPORARY VIEW sales(location, year, q1, q2, q3, q4) AS
VALUES ('Toronto' , 2020, 100 , 80 , 70, 150),
('San Francisco', 2020, NULL, 20 , 50, 60),
('Toronto' , 2021, 110 , 90 , 80, 170),
('San Francisco', 2021, 70 , 120, 85, 105);
query IIII rowsort
SELECT *
FROM sales UNPIVOT INCLUDE NULLS
(sales FOR quarter IN (q1 AS "Jan-Mar",
q2 AS "Apr-Jun",
q3 AS "Jul-Sep",
q4 AS "Oct-Dec"));
----
San Francisco 2020 Apr-Jun 20
San Francisco 2020 Jan-Mar NULL
San Francisco 2020 Jul-Sep 50
San Francisco 2020 Oct-Dec 60
San Francisco 2021 Apr-Jun 120
San Francisco 2021 Jan-Mar 70
San Francisco 2021 Jul-Sep 85
San Francisco 2021 Oct-Dec 105
Toronto 2020 Apr-Jun 80
Toronto 2020 Jan-Mar 100
Toronto 2020 Jul-Sep 70
Toronto 2020 Oct-Dec 150
Toronto 2021 Apr-Jun 90
Toronto 2021 Jan-Mar 110
Toronto 2021 Jul-Sep 80
Toronto 2021 Oct-Dec 170
statement ok
CREATE OR REPLACE TEMPORARY VIEW oncall
(year, week, area , name1 , email1 , phone1 , name2 , email2 , phone2) AS
VALUES (2022, 1 , 'frontend', 'Freddy', 'fred@alwaysup.org' , 15551234567, 'Fanny' , 'fanny@lwaysup.org' , 15552345678),
(2022, 1 , 'backend' , 'Boris' , 'boris@alwaysup.org', 15553456789, 'Boomer', 'boomer@lwaysup.org', 15554567890),
(2022, 2 , 'frontend', 'Franky', 'frank@lwaysup.org' , 15555678901, 'Fin' , 'fin@alwaysup.org' , 15556789012),
(2022, 2 , 'backend' , 'Bonny' , 'bonny@alwaysup.org', 15557890123, 'Bea' , 'bea@alwaysup.org' , 15558901234);
query IIIIIII rowsort
SELECT *
FROM oncall UNPIVOT ((name, email, phone) FOR precedence IN ((name1, email1, phone1) AS primary,
(name2, email2, phone2) AS secondary));
----
2022 1 backend primary Boris boris@alwaysup.org 15553456789
2022 1 backend secondary Boomer boomer@lwaysup.org 15554567890
2022 1 frontend primary Freddy fred@alwaysup.org 15551234567
2022 1 frontend secondary Fanny fanny@lwaysup.org 15552345678
2022 2 backend primary Bonny bonny@alwaysup.org 15557890123
2022 2 backend secondary Bea bea@alwaysup.org 15558901234
2022 2 frontend primary Franky frank@lwaysup.org 15555678901
2022 2 frontend secondary Fin fin@alwaysup.org 15556789012
|
