SQL SELECT Convert Min/Max into Separate Rows

Question

I have a table that has a min and max value that I'd like create a row for each valid number in a SELECT statement.

Original table:

| Foobar_ID | Min_Period | Max_Period |
---------------------------------------
| 1         | 0          | 2          |
| 2         | 1          | 4          |

I'd like to turn that into:

| Foobar_ID | Period_Num |
--------------------------
| 1         | 0          |
| 1         | 1          |
| 1         | 2          |
| 2         | 1          |
| 2         | 2          |
| 2         | 3          |
| 2         | 4          |

The SELECT results need to come out as one result-set, so I'm not sure if a WHILE loop would work in my case.

Answer 1

If you expect just a handful of rows per foobar, then this is a good opportunity to learn about recursive CTEs:

with cte as (
      select foobar_id, min_period as period_num, max_period
      from original t
      union all
      select foobar_id, min_period + 1 as period_num, max_period
      from cte
      where period_num < max_period
    )
select foobar_id, period_num
from cte
order by foobar_id, period_num;

You can extend this to any number of periods by setting the MAXRECURSION option to 0.