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

expected output

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

CodePudding user response：

You don't need loops here, you can use sequence or repeat combined 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:

CodePudding user response：

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)