SQL to generate range of valid values between 2 known integers

I need to write a SQL query that will generate a list of valid integers given the start and end to the range.

ie given the below table :-

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

I'd like to write a query which would return all valid values (against CMPY) ie :-

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

This needs to work on both Oracle and SQL开发者_Go百科 Server.

---

For a portable solution you may want to create a simple table of numbers like this:

create table integers (val integer);

then populate it with as many rows as you may ever need. Then your query is:

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

---

Solution for Oracle without any table (You can use the table way 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)                

e.g:

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)    

---

The most efficient way to do this is to have a Numbers(int num) table that you can query to get a range of numbers, e.g.:

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

---

Looking at your data, the numbers are years. You could create a table in both servers that contains all years, and then join on 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 in between, on both Oracle and SQL Server.