could you help me solve my problem, please?
I have table like:
name from to
x001 1 4
x002 9 12
x003 14 15
And I want to "loop" with numbers between from and to column and meanwhile keep name index with x00
like this result:
name number
x001 1
x001 2
x001 3
x001 4
x002 9
x002 10
x002 11
x002 12
x003 14
x003 15
I use snowflake.
BIG thank you in advance :)
The two steps are to make a range of data via GENERATOR which requires a fixed input, and then to make a sequence of number which SEQ4 can have gaps so that needs to be feed into a ROW_NUMBER to make sure there are no gaps.
Then you can join to the data range.
WITH gen AS (
SELECT ROW_NUMBER() OVER (ORDER BY SEQ4()) as rn
FROM TABLE(generator(rowcount => 1000))
)
SELECT a.name
,b.rn as number
FROM table AS a
JOIN gen AS b
ON b.rn BETWEEN a.from AND a.to
ORDER BY 1,2;
Try this
Select * from (Select name, "from" + row_number() over
(order by 1) fr
From
Table) t
Where
t.fr>(
Select rn from (
Select row_number() over (order by 1) rn
From
<some_big_table_with_counts=max(to)>
)
Where rn<=t.to)