CODEWITHSUNDEEP
sqlgenerate-seriesdremio
Muffex
Muffex

Reputation: 45

Imitate behaviour of "generate_series" with SQL

I am trying to imitate the behaviour of "generate_series". My table contains various fields. One of these is "count". I want to output each row as often as "count" because each row is going to serve as an independent object.

Dremio, however, does not support "generate_series".

Does anyone know how to accomplish the given task?

regards Muffex

Edit:

Table:

id name count
0123 ABC 3
2345 EFG 0
3456 HIK 2

Desired Output:

id name
0123 ABC
0123 ABC
0123 ABC
3456 HIK
3456 HIK

Upvotes: 0

Views: 1140

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can generate a list of numbers that are "big enough" and then joining. Assuming 100 is big enough and that your original table has at least 100 rows:

with n as (
      select row_number() over (order by null) as n
      from t
      limit 100
      )
select t.*, n.n
from t join
     n
     on n.n <= t.cnt;

Upvotes: 1

Related Questions