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

Question

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

Answer 1

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)