How do I transform this table with arrays in num and letter columns:
如何使用num和letter列中的数组转换此表:
id | num | letter
-----+-----------+-----------
111 | [1, 2] | [a, b]
111 | [3, 4] | [c, d]
222 | [5, 6, 7] | [e, f, g]
into this table
进入这张桌子
id | num | letter
-----+-----+--------
111 | 1 | a
111 | 2 | b
111 | 3 | c
111 | 4 | d
222 | 5 | e
222 | 6 | f
222 | 7 | g
Appendix: here is some sql to play around with to try to perform the transformation
附录:这里有一些sql可以尝试执行转换
with test as(
select * from (
values
(111, array[1,2], array['a','b']),
(111, array[3,4], array['c','d']),
(222, array[5,6,7], array['e','f', 'g'])
) as t (id, num, letter)
)
select
*
from test
1 个解决方案
#1
3
PrestoDB seems to support unnest()
with multiple arguments:
PrestoDB似乎支持带有多个参数的unnest():
select t.id, u.n, u.l
from test cross join
unnest(num, letter) as u(n, l)
#1
3
PrestoDB seems to support unnest()
with multiple arguments:
PrestoDB似乎支持带有多个参数的unnest():
select t.id, u.n, u.l
from test cross join
unnest(num, letter) as u(n, l)