SQL to generate a range of valid values ​​between two known integers

Question

I need to write an SQL query that will generate a list of real integers, given the beginning and end of the range.

i.e., given the table below: -

CMPY------MIN_YEAR------MAX_YEAR
PS--------2007----------2014

I would like to write a query that will return all valid values ​​(against CMPY), that is: -

CMPY  YEAR
PS    2007
PS    2008
PS    2009
PS    2010
PS    2011
PS    2012
PS    2013
PS    2014

This should work on both Oracle and SQL Server.

Answer 1

For a portable solution, you can create a simple number table, for example:

create table integers (val integer);

then fill it with the number of lines you may need. Then your request:

select t.cmpy, i.val
from mytable t
join integers i on i.val between t.min_year and t.max_year;

Answer 2

Solution for Oracle without any table (you can use the table method for SQL Server):

SELECT a.cmpy, (a.min_year - 1) + LEVEL MIN_YEAR 
    FROM  YOUR_TABLE  a
  CONNECT BY (LEVEL-1) <= (MAX_YEAR - MIN_YEAR)                

eg:

SELECT a.cmpy, (a.min_year - 1) + LEVEL MIN_YEAR 
    FROM (
                  SELECT 'PS' CMPY, 2007 MIN_YEAR, 2014 MAX_YEAR
                  FROM DUAL
                ) a
  CONNECT BY (LEVEL-1) <= (MAX_YEAR - MIN_YEAR)    

Answer 3

Looking at your data, the numbers are years. You can create a table on both servers that contains all the years, and then join it:

select  year.YearNr
from    YourTable yt
join    YearTable year
on      year.YearNr between yt.StartDate and yt.EndDate

This effectively creates a row for each year between them, both on Oracle and on SQL Server.

Answer 4

The most efficient way to do this is to have a Numbers table (int num), which you can query to get a range of numbers, for example:

SELECT C.CMPY, N.num
FROM CMPY AS C
JOIN Numbers AS N
  ON N.num BETWEEN C.MIN_YEAR AND C.MAX_YEAR