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
|
# name: test/sql/pivot/optional_pivots.test
# description: Test pivot syntax with various optional statements
# 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);
query I
PIVOT Cities USING SUM(Population);
----
30100
query II rowsort
PIVOT Cities USING SUM(Population) GROUP BY Country;
----
NL 3228
US 26872
query I rowsort
PIVOT Cities GROUP BY Country;
----
NL
US
query IIII rowsort
PIVOT Cities ON Year GROUP BY Country;
----
NL 1 1 1
US 2 2 2
query IIII rowsort
PIVOT (SELECT Country, Year FROM Cities) ON Year;
----
NL 1 1 1
US 2 2 2
|
