SQLite - Repeat rows based on column values

user_8194358 0
Viewed 197

Here is how dataset looks like

Key1  Start  End
 A     5      8
 B     2      4

I am looking for output as shown below where a new column "Num" is created and it should start with respective row value of "Start" and increment by +1 and stop at "End" number. The final result should be able to save in a new table.

Key1 Num Start End
A     5   5     8
A     6   5     8
A     7   5     8
A     8   5     8
B     2   2     4
B     3   2     4
B     4   2     4

I could find some solutions in Sqlserver but not in SQLite.

sqlite
2 Answers

You can do it with recursive CTE:

with cte as (
  select Key1, Start Num, Start, End
  from tablename
  union all
  select Key1, Num + 1, Start, End
  from cte 
  where Num < End
)  
select * from cte
order by Key1, Start, End, Num

See the demo.
Results:

| Key1 | Num | Start | End |
| ---- | --- | ----- | --- |
| A    | 5   | 5     | 8   |
| A    | 6   | 5     | 8   |
| A    | 7   | 5     | 8   |
| A    | 8   | 5     | 8   |
| B    | 2   | 2     | 4   |
| B    | 3   | 2     | 4   |
| B    | 4   | 2     | 4   |
user_104988280

I have no experience in SQLlite but feel below should help you:

SELECT key1, (Select value from generate_series(start, end,1)) as Num, Start, End from table;

You need to get generate_series extension installed for this.

user_139920950
Related