Search code examples
sqlgenerate-seriesdremio

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
Solution

  • 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;