How to create rows based on the range of all values between min and max in Snowflake (SQL)?-CodePudding

Assume I have the following data:

ID	T_Min	T_Max
1	3	5
2	1	4

I would like to create the following table using SQL (Snowflake):

ID	T
1	3
1	4
1	5
2	1
2	2
2	3
2	4

Does someone know how to do this? Thank you very much in advance!

CodePudding user response：

Sample data:

CREATE OR REPLACE TABLE T1 (
    ID INT,
    T_Min INT,
    T_Max INT);

INSERT INTO T1(ID, T_Min, T_Max)
SELECT * FROM VALUES (1, 3, 5), (2, 1, 4) t(ID, T_Min, T_Max);

Solution:

WITH N AS (
  SELECT SEQ4()   1 AS T FROM TABLE(GENERATOR(ROWCOUNT => 1000)) -- Set to the maximum value of the difference between T_Max and T_Min
)
SELECT T1.ID, N.T
  FROM T1
  JOIN N ON N.T BETWEEN T1.T_Min AND T1.T_Max
 ORDER BY T1.ID, N.T;