Using sql to recursively generate values depending on keys and column values

I have a table like this:

store	item	value
store1	item1	2
store1	item2	3

I want to use it to create the following table:

store	item	value
store1	item1	0
store1	item1	1
store1	item1	2
store1	item2	0
store1	item2	1
store1	item2	2
store1	item2	3

I’m aware that I can do with recursive n(n) as (select 0 n union all select n + 1 from n limit 40) and then later a cross join if the upper limit was constant (40) but in my case, it changes by (store, item). I was wondering if this can be done in SQL or if it is better to figure it out in python. Thanks.

Answer

You can use a recursive CTE. The syntax is something like this:

with recursive cte(store, item, value, n) as (
      select store, item, value, 0
      from t
      union all
      select store, item, value, n + 1
      from cte
      where n < value
     )
select store, item, n as value
from cte;

The exact syntax for recursive CTEs varies, depending on the database. This appears to be similar to the code you have in your question.

Here is a db<>fiddle.