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
|
# name: test/sql/pivot/pivot_in_subquery.test
# description: Test SQL pivot with a subquery in the IN clause
# 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);
# order the pivot columns
query IIIII rowsort
PIVOT Cities ON Year IN (SELECT Year FROM Cities ORDER BY Year DESC) USING SUM(Population);
----
NL Amsterdam 1158 1065 1005
US New York City 8772 8175 8015
US Seattle 738 608 564
query IIIII rowsort
PIVOT Cities ON Year IN (SELECT YEAR FROM (SELECT Year, SUM(POPULATION) AS popsum FROM Cities GROUP BY Year ORDER BY popsum DESC)) USING SUM(Population);
----
NL Amsterdam 1158 1065 1005
US New York City 8772 8175 8015
US Seattle 738 608 564
query IIIII rowsort
PIVOT Cities ON Year IN (SELECT '2010' UNION ALL SELECT '2000' UNION ALL SELECT '2020') USING SUM(Population);
----
NL Amsterdam 1065 1005 1158
US New York City 8175 8015 8772
US Seattle 608 564 738
statement error
PIVOT Cities ON Year IN (SELECT xx FROM Cities) USING SUM(Population);
----
<REGEX>:Binder Error.*Referenced column.*not found.*
|
