Imitate behaviour of "generate_series" with SQL

Question

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

Does your database support analytic functions? Can you include sample data in your question? — Tim Biegeleisen, Jul 12 '21 at 08:32
Dremio does provide these functions: https://docs.dremio.com/sql-reference/sql-functions/ — Muffex, Jul 12 '21 at 15:39

score 1 · Accepted Answer · answered Jul 12 '21 at 10:17

1

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;

answered Jul 12 '21 at 10:17

Gordon Linoff

1,242,037
58
646
786

I am VERY grateful for your reply. The output is as intended. The caveat is that "count" can differ greatly and performance might suffer under the current solution. If there is no (even) better alternative, I will accept this solution. – Muffex Jul 12 '21 at 21:07

Imitate behaviour of "generate_series" with SQL

1 Answers1