0

I have a table called example that looks as follows:

ID | MIN | MAX |
1  | 1   |  5  |
2  | 34  | 38  |

I need to take each ID and loop from it's min to max, incrementing by 2 and thus get the following WITHOUT using INSERT statements, thus in a SELECT:

ID | INDEX | VALUE
1  |  1    |   1
1  |  2    |   3
1  |  3    |   5
2  |  1    |   34
2  |  2    |   36
2  |  3    |   38

Any ideas of how to do this?

Werner Raath
  • 1,322
  • 3
  • 16
  • 34

2 Answers2

3

The set-returning function generate_series does exactly that:

SELECT
  id,
  generate_series(1, (max-min)/2+1) AS index,
  generate_series(min, max, 2) AS value
FROM
  example;

(online demo)

The index can alternatively be generated with RANK() (example, see also @a_horse_­with_­no_­name's answer) if you don't want to rely on the parallel sets.

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
2

Use generate_series() to generate the numbers and a window function to calculate the index:

select e.id, 
       row_number() over (partition by e.id order by g.value) as index,
       g.value
from example e
  cross join generate_series(e.min, e.max, 2) as g(value);