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
|
# name: test/sql/pivot/pivot_expressions.test
# description: Test SQL pivot expressions
# 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 IIII rowsort
PIVOT Cities ON Country || '_' || Name USING SUM(Population) GROUP BY Year;
----
2000 1005 8015 564
2010 1065 8175 608
2020 1158 8772 738
query II rowsort
PIVOT Cities ON (CASE WHEN Country='NL' THEN NULL ELSE Country END) USING SUM(Population) GROUP BY Year;
----
2000 8579
2010 8783
2020 9510
# we allow expressions in the aggregate as well
query IIII rowsort
PIVOT Cities ON Country || '_' || Name USING COALESCE(SUM(Population), 0) GROUP BY Year;
----
2000 1005 8015 564
2010 1065 8175 608
2020 1158 8772 738
# casts
query IIII rowsort
PIVOT Cities ON Country || '_' || Name USING SUM(Population)::VARCHAR GROUP BY Year;
----
2000 1005 8015 564
2010 1065 8175 608
2020 1158 8772 738
# functions
query IIII rowsort
PIVOT Cities ON Country || '_' || Name USING SUM(Population) + 42 GROUP BY Year;
----
2000 1047 8057 606
2010 1107 8217 650
2020 1200 8814 780
# we cannot have multiple aggregates
statement error
PIVOT Cities ON Country || '_' || Name USING SUM(Population) + COUNT(*) GROUP BY Year;
----
Pivot expression must contain exactly one aggregate
# columns can only be referenced within the aggregate
statement error
PIVOT Cities ON Country || '_' || Name USING SUM(Population) + Population GROUP BY Year;
----
Columns can only be referenced within the aggregate of a PIVOT expression
statement error
PIVOT Cities ON min(Country) over () USING SUM(Population) GROUP BY Year;
----
cannot contain window functions
statement error
PIVOT Cities ON min(Country) USING SUM(Population) GROUP BY Year;
----
cannot contain aggregates
statement error
PIVOT Cities ON NULL USING SUM(Population) GROUP BY Year;
----
Cannot pivot on constant value
statement error
PIVOT Cities ON 'hello world' USING SUM(Population) GROUP BY Year;
----
Cannot pivot on constant value
statement error
PIVOT Cities ON (SELECT COUNTRY) USING SUM(Population) GROUP BY Year;
----
Cannot pivot on subquery
|
