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
|
# name: test/sql/pivot/pivot_subquery.test
# description: Test pivot over subquery
# group: [pivot]
statement ok
PRAGMA enable_verification
# pivot using an enum
statement ok
CREATE OR REPLACE TABLE sales(empid INT, amount INT, d DATE);
statement ok
INSERT INTO sales VALUES
(1, 10000, DATE '2000-01-01'),
(1, 400, DATE '2000-01-07'),
(2, 4500, DATE '2001-01-21'),
(2, 35000, DATE '2001-01-21'),
(1, 5000, DATE '2000-02-03'),
(1, 3000, DATE '2000-02-07'),
(2, 200, DATE '2001-02-05'),
(2, 90500, DATE '2001-02-19'),
(1, 6000, DATE '2000-03-01'),
(1, 5000, DATE '2000-03-09'),
(2, 2500, DATE '2001-03-03'),
(2, 9500, DATE '2001-03-08');
# PIVOT over a subquery
query IIIIIII
PIVOT (SELECT YEAR(d) AS year, MONTH(d) AS month, empid, amount FROM sales) ON YEAR, MONTH USING SUM(AMOUNT) ORDER BY ALL;
----
1 10400 8000 11000 NULL NULL NULL
2 NULL NULL NULL 39500 90700 12000
# PIVOT over a PIVOT
query II
PIVOT (PIVOT (SELECT YEAR(d) AS year, MONTH(d) AS month, empid, amount FROM sales) ON YEAR, MONTH USING SUM(AMOUNT))
ON empid USING SUM(COALESCE("2000_1",0) + COALESCE("2000_2",0) + COALESCE("2000_3",0) + COALESCE("2001_1",0) + COALESCE("2001_2",0) + COALESCE("2001_3",0))
----
29400 142200
statement error
CREATE VIEW pivot_view AS PIVOT (SELECT YEAR(d) AS year, MONTH(d) AS month, empid, amount FROM sales) ON YEAR, MONTH USING SUM(AMOUNT);
----
cannot be used in views
statement error
CREATE MACRO xt2(a) as TABLE PIVOT sales ON d USING SUM(amount)
----
cannot be used in macros
statement error
CREATE MACRO xt2(a) as (PIVOT sales ON d USING SUM(amount))
----
cannot be used in macros
|
