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
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
|
# name: test/sql/pivot/pivot_example.test_slow
# description: Test SQL pivot examples for documentation
# group: [pivot]
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE Cities(Country VARCHAR, Name VARCHAR, Year INT, Population INT);
statement ok
INSERT INTO Cities VALUES ('NL', 'Amsterdam', 2000, 1005);
statement ok
INSERT INTO Cities VALUES ('NL', 'Amsterdam', 2010, 1065);
statement ok
INSERT INTO Cities VALUES ('NL', 'Amsterdam', 2020, 1158);
statement ok
INSERT INTO Cities VALUES ('US', 'Seattle', 2000, 564);
statement ok
INSERT INTO Cities VALUES ('US', 'Seattle', 2010, 608);
statement ok
INSERT INTO Cities VALUES ('US', 'Seattle', 2020, 738);
statement ok
INSERT INTO Cities VALUES ('US', 'New York City', 2000, 8015);
statement ok
INSERT INTO Cities VALUES ('US', 'New York City', 2010, 8175);
statement ok
INSERT INTO Cities VALUES ('US', 'New York City', 2020, 8772);
statement ok
SET pivot_filter_threshold=99
loop i 0 2
statement ok
PIVOT Cities ON Country, Name IN ('xx') USING SUM(Population);
query IIIII rowsort
PIVOT Cities ON Year USING SUM(Population);
----
NL Amsterdam 1005 1065 1158
US New York City 8015 8175 8772
US Seattle 564 608 738
query IIIII rowsort
SELECT Country, Name, "2000_total_pop", "2010_total_pop", "2020_total_pop" FROM (PIVOT Cities ON Year USING SUM(Population) as total_pop)
----
NL Amsterdam 1005 1065 1158
US New York City 8015 8175 8772
US Seattle 564 608 738
query IIIII rowsort
PIVOT_WIDER Cities ON Year USING SUM(Population);
----
NL Amsterdam 1005 1065 1158
US New York City 8015 8175 8772
US Seattle 564 608 738
# sql syntax
query IIIII rowsort
FROM Cities PIVOT (SUM(Population) FOR Year IN (2000, 2010, 2020));
----
NL Amsterdam 1005 1065 1158
US New York City 8015 8175 8772
US Seattle 564 608 738
query IIII rowsort
PIVOT Cities ON Year IN (2000, 2020) USING SUM(Population);
----
NL Amsterdam 1005 1158
US New York City 8015 8772
US Seattle 564 738
query IIII rowsort
PIVOT Cities ON Year USING SUM(Population) GROUP BY Country;
----
NL 1005 1065 1158
US 8579 8783 9510
# use pivots in a set operation
statement ok
PIVOT Cities ON Year USING SUM(Population)
UNION ALL BY NAME
PIVOT Cities ON Name USING SUM(Population);
# join on pivots
query IIIIIII rowsort
FROM
(PIVOT Cities ON Year USING SUM(Population) GROUP BY Country)
JOIN
(PIVOT Cities ON Name USING SUM(Population) GROUP BY Country)
USING (Country)
----
NL 1005 1065 1158 3228 NULL NULL
US 8579 8783 9510 NULL 24962 1910
query II rowsort
PIVOT Cities ON (Country, Name) IN ('xx') USING SUM(Population);
----
2000 NULL
2010 NULL
2020 NULL
query IIIIIII rowsort
PIVOT (SELECT Country, Population, Year FROM Cities) ON Year USING SUM(Population) as sum_pop, count(population) as count_pop,;
----
NL 1005 1 1065 1 1158 1
US 8579 2 8783 2 9510 2
# multiple pivots
query IIIIIII rowsort
PIVOT Cities ON Year USING SUM(Population) as sum_pop, count(population) as count_pop, GROUP BY Country;
----
NL 1005 1 1065 1 1158 1
US 8579 2 8783 2 9510 2
query IIIIIII rowsort
PIVOT Cities ON Year USING SUM(Population), count(population) GROUP BY Country;
----
NL 1005 1 1065 1 1158 1
US 8579 2 8783 2 9510 2
# pivot order by/limit
query IIII
PIVOT Cities ON Year USING SUM(Population) GROUP BY country ORDER BY country desc
----
US 8579 8783 9510
NL 1005 1065 1158
query IIII
PIVOT Cities ON Year USING SUM(Population) GROUP BY country ORDER BY country desc LIMIT 1
----
US 8579 8783 9510
query IIII
PIVOT Cities ON Year USING SUM(Population) GROUP BY country ORDER BY country LIMIT 1
----
NL 1005 1065 1158
query IIII
PIVOT Cities ON Year USING SUM(Population) GROUP BY country ORDER BY country LIMIT 1 OFFSET 1
----
US 8579 8783 9510
query IIII
PIVOT Cities ON Year USING SUM(Population) GROUP BY country ORDER BY ALL
----
NL 1005 1065 1158
US 8579 8783 9510
statement ok
SET pivot_filter_threshold=0
endloop
# unpivot
statement ok
CREATE TABLE PivotedCities AS PIVOT Cities ON Year USING SUM(Population);
query IIII nosort unpivot
UNPIVOT PivotedCities ON 2000, 2010, 2020 INTO NAME Year VALUE Population;
----
query IIII nosort unpivot
FROM PivotedCities UNPIVOT(Population FOR Year IN (2000, 2010, 2020));
----
query IIII nosort unpivot
UNPIVOT PivotedCities ON 2000, 2010, 2020;
----
query IIII nosort unpivot
UNPIVOT PivotedCities ON COLUMNS('\d+');
----
query IIII nosort unpivot
UNPIVOT PivotedCities ON * EXCLUDE (Country, Name)
----
query IIII nosort unpivot
PIVOT_LONGER PivotedCities ON 2000, 2010, 2020;
# unpivot order by/limit
query IIII
UNPIVOT PivotedCities ON 2000, 2010, 2020 ORDER BY ALL DESC LIMIT 1
----
US Seattle 2020 738
query IIII
UNPIVOT PivotedCities ON 2000, 2010, 2020 ORDER BY ALL LIMIT 1
----
NL Amsterdam 2000 1005
query IIII
UNPIVOT PivotedCities ON 2000, 2010, 2020 ORDER BY 1, 3 LIMIT 1 OFFSET 1
----
NL Amsterdam 2010 1065
|
