Combine each of n ids with m consecutive numbers in SQL

Question

I have a set of n ids, say table 1, like so:

id
id_1
id_2
...
id_n

I would like to combine this with a set of numbers 1...m (m is medium large here, i.e. between a hundred and a thousand) to get a table that looks like this:

id	number
id_1	1
id_1	2
...	...
id_1	m
id_2	1
...	...
id_2	m
...	...
id_n	m

That is the size of this, say table 2, is going to be m*n.

Is something like this possible in SQL? The dialect I am using is Snowflake.

Error_2646 · Accepted Answer · 2021-03-03T19:01:41.537

3

Just a cross join should work right?

Substituting "10" for m

SELECT IT.id,
       INT_SEQ.seq_val
  FROM ID_TABLE IT
 CROSS
  JOIN
       (select row_number() over(order by 0) AS seq_val
          from table(generator(rowcount => 10)) v 
       ) INT_SEQ

edited Mar 03 '21 at 19:01

answered Mar 03 '21 at 18:56

Error_2646

2,555
1
10
22

Careful: `seq4()` might not give sequential numbers, add some `row_number() over(order by 0)` there https://stackoverflow.com/a/66449068/132438 – Felipe Hoffa Mar 03 '21 at 18:59
I guess I did not know about this generator think. This is snowflake right? – clog14 Mar 03 '21 at 19:01
@FelipeHoffa Great catch – Error_2646 Mar 03 '21 at 19:02
@clog14 Yup, just tested it out – Error_2646 Mar 03 '21 at 19:05
I'm glad error_2646 answer worked for you @clog14! Please remember to upvote and/or accept useful answers ;) – Felipe Hoffa Mar 03 '21 at 19:15
Have to still try it tomorrow but then will for sure do all these things ;-) – clog14 Mar 03 '21 at 19:16

Combine each of n ids with m consecutive numbers in SQL

1 Answers1