user5226582 user5226582 - 4 months ago 10
SQL Question

SQL Select each row multiple times depending on the row value

Lets say I have a table of this form:

ID value
1 2
2 1
3 4


What's the most efficient way to select each row
value
times?

The desired output of the query is

ID
1
1
2
3
3
3
3

Answer

If you have a numbers table, you can do:

select t.id
from t join
     numbers n
     on n.n <= t.value;

There are various ways to construct a numbers table. Here is a brute force way that works in most databases:

select t.id
from t join
     (select 1 as n union all select 2 union all select 3) n
     on n.n <= t.value;