CODEWITHSUNDEEP
sqllistprestoexpand
Amir
Amir

Reputation: 318

SQL/Presto expanding a list column into multiple rows

Given a hive table like

a  |   b
----------
1  |  [1,2,3]
2  |  [2,3]

How can I create a secondary table like

a | b
------
1 | 1
1 | 2 
1 | 3 
2 | 2 
2 | 3

Upvotes: 2

Views: 1437

Answers (1)

Guru Stron
Guru Stron

Reputation: 142008

This is a textbook case for unnest:

-- sample data
WITH dataset (a, b) AS (
    VALUES (1, array[1,2,3]),
    (2, array[2,3])
) 

-- query
select a, bb
from dataset
cross join unnest(b) as t(bb)
order by a, bb -- to pretify output

Output:

a bb
1 1
1 2
1 3
2 2
2 3

Upvotes: 2

Related Questions