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
|
# name: test/sql/pivot/pivot_storage.test
# description: Test storage of views with pivots
# group: [pivot]
# load the DB from disk
load __TEST_DIR__/pivot_storage.db
statement ok
PRAGMA enable_verification
statement ok
CREATE OR REPLACE TABLE monthly_sales(empid INT, amount INT, month TEXT);
statement ok
INSERT INTO monthly_sales VALUES
(1, 10000, 'JAN'),
(1, 400, 'JAN'),
(2, 4500, 'JAN'),
(2, 35000, 'JAN'),
(1, 5000, 'FEB'),
(1, 3000, 'FEB'),
(2, 200, 'FEB'),
(2, 90500, 'FEB'),
(1, 6000, 'MAR'),
(1, 5000, 'MAR'),
(2, 2500, 'MAR'),
(2, 9500, 'MAR'),
(1, 8000, 'APR'),
(1, 10000, 'APR'),
(2, 800, 'APR'),
(2, 4500, 'APR');
statement ok
CREATE VIEW v1 AS SELECT *
FROM monthly_sales
PIVOT(SUM(amount) FOR MONTH IN ('JAN', 'FEB', 'MAR', 'APR'))
AS p
ORDER BY EMPID;
statement ok
CREATE MACRO pivot_macro(val)
as TABLE SELECT *
FROM monthly_sales
PIVOT(SUM(amount + val) FOR MONTH IN ('JAN', 'FEB', 'MAR', 'APR'))
AS p
ORDER BY EMPID;
query IIIII
FROM v1;
----
1 10400 8000 11000 18000
2 39500 90700 12000 5300
query IIIII
FROM pivot_macro(1);
----
1 10402 8002 11002 18002
2 39502 90702 12002 5302
restart
query IIIII
FROM v1;
----
1 10400 8000 11000 18000
2 39500 90700 12000 5300
query IIIII
FROM pivot_macro(1);
----
1 10402 8002 11002 18002
2 39502 90702 12002 5302
|
