"Loop" SQL db from column ranges // Snowflake-CodePudding

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.

tables

BIG thank you in advance :)

CodePudding user response：

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;

CodePudding user response：

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)