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
|
# name: test/sql/pivot/test_unpivot_stmt.test
# description: Test unpivot of the stack-overflow example - https://stackoverflow.com/questions/72922418/create-rows-from-part-of-column-names/72939299
# group: [pivot]
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE t1(id BIGINT, "Sales (05/19/2020)" BIGINT, "Sales (06/03/2020)" BIGINT, "Sales (10/23/2020)" BIGINT);
statement ok
INSERT INTO t1 VALUES(10629465, 23, 47, 99);
statement ok
INSERT INTO t1 VALUES(98765432, 10, 99, 33);
# SQL standard syntax
query III
SELECT id, regexp_replace(date, 'Sales [(]([0-9]+)/([0-9]+)/([0-9]+)[)]', '\3-\1-\2')::DATE AS date, sales
FROM t1
UNPIVOT (sales FOR date IN ("Sales (05/19/2020)", "Sales (06/03/2020)", "Sales (10/23/2020)"))
ORDER BY ALL;
----
10629465 2020-05-19 23
10629465 2020-06-03 47
10629465 2020-10-23 99
98765432 2020-05-19 10
98765432 2020-06-03 99
98765432 2020-10-23 33
# base unpivot statement
query III
SELECT id, regexp_replace(date, 'Sales [(]([0-9]+)/([0-9]+)/([0-9]+)[)]', '\3-\1-\2')::DATE AS date, sales
FROM
(UNPIVOT t1 ON "Sales (05/19/2020)", "Sales (06/03/2020)", "Sales (10/23/2020)" INTO NAME date VALUE sales)
ORDER BY ALL;
----
10629465 2020-05-19 23
10629465 2020-06-03 47
10629465 2020-10-23 99
98765432 2020-05-19 10
98765432 2020-06-03 99
98765432 2020-10-23 33
# alias
query III
SELECT *
FROM
(UNPIVOT t1 ON "Sales (05/19/2020)" AS "2020-05-19", "Sales (06/03/2020)" AS "2020-06-03", "Sales (10/23/2020)" AS "2020-10-23" INTO NAME date VALUE sales)
ORDER BY ALL;
----
10629465 2020-05-19 23
10629465 2020-06-03 47
10629465 2020-10-23 99
98765432 2020-05-19 10
98765432 2020-06-03 99
98765432 2020-10-23 33
# regex in UNPIVOT
query III
SELECT id, regexp_replace(date, 'Sales [(]([0-9]+)/([0-9]+)/([0-9]+)[)]', '\3-\1-\2')::DATE AS date, sales FROM t1
UNPIVOT (Sales FOR Date IN (COLUMNS('Sales.*')))
ORDER BY ALL;
----
10629465 2020-05-19 23
10629465 2020-06-03 47
10629465 2020-10-23 99
98765432 2020-05-19 10
98765432 2020-06-03 99
98765432 2020-10-23 33
# UNPIVOT statement
query III
SELECT id, regexp_replace(date, 'Sales [(]([0-9]+)/([0-9]+)/([0-9]+)[)]', '\3-\1-\2')::DATE AS date, sales
FROM
(UNPIVOT t1 ON COLUMNS('Sales.*') INTO NAME date VALUE sales)
ORDER BY ALL;
----
10629465 2020-05-19 23
10629465 2020-06-03 47
10629465 2020-10-23 99
98765432 2020-05-19 10
98765432 2020-06-03 99
98765432 2020-10-23 33
# exclude clause
query III
SELECT id, regexp_replace(date, 'Sales [(]([0-9]+)/([0-9]+)/([0-9]+)[)]', '\3-\1-\2')::DATE AS date, sales
FROM
(UNPIVOT t1 ON * EXCLUDE (id) INTO NAME date VALUE sales)
ORDER BY ALL;
----
10629465 2020-05-19 23
10629465 2020-06-03 47
10629465 2020-10-23 99
98765432 2020-05-19 10
98765432 2020-06-03 99
98765432 2020-10-23 33
|
