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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
|
# name: test/sql/pivot/test_unpivot.test
# description: Test standard SQL unpivot syntax
# group: [pivot]
# example taken from the snowflake docs
# https://docs.snowflake.com/en/sql-reference/constructs/unpivot
statement ok
PRAGMA enable_verification
statement ok
CREATE OR REPLACE TABLE monthly_sales(empid INT, dept TEXT, Jan INT, Feb INT, Mar INT, April INT);
statement ok
INSERT INTO monthly_sales VALUES
(1, 'electronics', 100, 200, 300, 100),
(2, 'clothes', 100, 300, 150, 200),
(3, 'cars', 200, 400, 100, 50);
# standard UNPIVOT
query IIII
SELECT * FROM monthly_sales
UNPIVOT(sales FOR month IN (jan, feb, mar, april))
ORDER BY empid;
----
1 electronics Jan 100
1 electronics Feb 200
1 electronics Mar 300
1 electronics April 100
2 clothes Jan 100
2 clothes Feb 300
2 clothes Mar 150
2 clothes April 200
3 cars Jan 200
3 cars Feb 400
3 cars Mar 100
3 cars April 50
# leaving out an entry works - it just does not unpivot that entry and leave it as a normal column
query IIIII
SELECT empid, dept, april, month, sales FROM monthly_sales
UNPIVOT(sales FOR month IN (jan, feb, mar))
ORDER BY empid;
----
1 electronics 100 Jan 100
1 electronics 100 Feb 200
1 electronics 100 Mar 300
2 clothes 200 Jan 100
2 clothes 200 Feb 300
2 clothes 200 Mar 150
3 cars 50 Jan 200
3 cars 50 Feb 400
3 cars 50 Mar 100
# alias in pivots
query IIII
SELECT * FROM monthly_sales
UNPIVOT(sales FOR month IN (jan AS January, feb AS February, mar AS March, april))
ORDER BY empid;
----
1 electronics January 100
1 electronics February 200
1 electronics March 300
1 electronics April 100
2 clothes January 100
2 clothes February 300
2 clothes March 150
2 clothes April 200
3 cars January 200
3 cars February 400
3 cars March 100
3 cars April 50
# alias the unpivot clause
query IIII
SELECT p.id, p.type, p.m, p.vals FROM monthly_sales
UNPIVOT(sales FOR month IN (jan, feb, mar, april)) AS p(id, type, m, vals);
----
1 electronics Jan 100
1 electronics Feb 200
1 electronics Mar 300
1 electronics April 100
2 clothes Jan 100
2 clothes Feb 300
2 clothes Mar 150
2 clothes April 200
3 cars Jan 200
3 cars Feb 400
3 cars Mar 100
3 cars April 50
# multi-column unpivot
query IIIII
SELECT empid, dept, month, sales_jan_feb, sales_mar_apr FROM monthly_sales
UNPIVOT((sales_jan_feb, sales_mar_apr) FOR month IN ((jan, feb), (mar, april)));
----
1 electronics Jan_Feb 100 200
1 electronics Mar_April 300 100
2 clothes Jan_Feb 100 300
2 clothes Mar_April 150 200
3 cars Jan_Feb 200 400
3 cars Mar_April 100 50
# multiple names in FOR clause
statement error
SELECT * FROM monthly_sales
UNPIVOT((sales_jan_feb, sales_mar_apr) FOR (month, month2) IN ((jan, feb), (mar, april)));
----
UNPIVOT requires a single column name
# count mismatch
statement error
SELECT * FROM monthly_sales
UNPIVOT(sales_jan_feb FOR month IN ((jan, feb), (mar, april)));
----
UNPIVOT name count mismatch
statement error
SELECT * FROM monthly_sales
UNPIVOT((a, b, c) FOR month IN ((jan, feb), (mar, april)));
----
UNPIVOT name count mismatch
statement error
SELECT empid, dept, month, sales_jan_feb, sales_mar_apr FROM monthly_sales
UNPIVOT((sales_jan_feb, sales_mar_apr) FOR month IN ((jan, feb), mar));
----
UNPIVOT value count mismatch
# unrecognized entry in unpivot
statement error
SELECT empid, dept, april, month, sales FROM monthly_sales
UNPIVOT(sales FOR month IN (jan, feb, mar, dec))
ORDER BY empid;
----
referenced in UNPIVOT but no matching entry was found in the table
# mix of types in pivot
statement error
SELECT * FROM monthly_sales
UNPIVOT(sales FOR month IN (empid, dept, jan, feb, mar, april))
----
an explicit cast is required
# unpivot over subquery
query IIIII
UNPIVOT (SELECT * FROM monthly_sales)
ON jan, feb, mar april
INTO
NAME month
VALUE sales;
----
1 electronics 100 Jan 100
1 electronics 100 Feb 200
1 electronics 100 april 300
2 clothes 200 Jan 100
2 clothes 200 Feb 300
2 clothes 200 april 150
3 cars 50 Jan 200
3 cars 50 Feb 400
3 cars 50 april 100
# empty IN clause
statement error
SELECT * FROM monthly_sales
UNPIVOT(sales FOR month IN ())
ORDER BY empid;
----
syntax error
statement error
SELECT * FROM monthly_sales
UNPIVOT(sales FOR month IN (''))
ORDER BY empid;
----
empty column name
statement error
SELECT * FROM monthly_sales
UNPIVOT(SUM(sales) FOR month IN (empid, dept, jan, feb, mar, april))
----
syntax error
|
