Explode a row into multiple rows based on a column value presto

user_1939183 0
Viewed 102

I am looking to explode a row into multiple rows based on a column[integer] value, I am trying to do this using presto

Below is an example

id count
1 5
2 2

expected output

id count
1 5
1 5
1 5
1 5
1 5
2 2
2 2

in the above example, id 1 has to be repeated 5 times and id 2 has to be repeated 2 times based on the count. Based on my experience, presto doesnt support recursive CTE.

Any help would be appreciated.

Thanks

presto sql
2 Answers

You don't need recursive CTE's here, you can use sequence or repeat to generate an array for corresponding length and then flatten it with unnest:

-- sample data
WITH dataset (id, count) AS (
   VALUES (1, 5),
    (2, 2)
)

-- query
select id, count
from dataset,
unnest (repeat(id, count)) as t (ignore)
-- unnest (sequence(0, count - 1)) as t (ignore)

Output:

id count
1 5
1 5
1 5
1 5
1 5
2 2
2 2
user_25012790

You could make the count into array with REPEAT and then CROSS JOIN.

Your input:

CREATE TABLE test AS
SELECT id, count
FROM (
  VALUES
    (1, 5),
    (2, 2)
) AS x (id, count)

Then:

SELECT id, t.count
FROM test
CROSS JOIN UNNEST(repeat(count, count)) AS t (count)
user_104836920
Related