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
|
# name: test/sql/pivot/pivot_enum.test
# description: Test SQL pivot syntax using an enum instead of an IN list
# group: [pivot]
statement ok
PRAGMA enable_verification
# pivot using an enum
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 TYPE unique_months AS ENUM (SELECT DISTINCT month FROM monthly_sales ORDER BY
CASE month WHEN 'JAN' THEN 1 WHEN 'FEB' THEN 2 WHEN 'MAR' THEN 3 ELSE 4 END);
query IIIII
SELECT *
FROM monthly_sales
PIVOT(SUM(amount) FOR MONTH IN unique_months)
AS p
ORDER BY EMPID;
----
1 10400 8000 11000 18000
2 39500 90700 12000 5300
# enum does not exist
statement error
SELECT *
FROM monthly_sales
PIVOT(SUM(amount) FOR MONTH IN unique_monthsx)
AS p
ORDER BY EMPID;
----
unique_monthsx
statement ok
CREATE TYPE not_an_enum AS VARCHAR;
# enum does not exist
statement error
SELECT *
FROM monthly_sales
PIVOT(SUM(amount) FOR MONTH IN not_an_enum)
AS p
ORDER BY EMPID;
----
Pivot must reference an ENUM type
|
