how to select last 12 months name and year without using tables using sql query?

Question

how can i select last 12 month name using current time. without using any table if current month is October then i want the result as

month    year
oct      2011
nov      2011
dec      2011
jan      2012
feb      2012
mar      2012
apr      2012
may      2012
jun      2012
jul      2012
aug      2012
sep      2012
oct      2012

i don't have any table in Database for this.

Answer 1

SET LANGUAGE English;

WITH R(N) AS
(
SELECT 0
UNION ALL
SELECT N+1 
FROM R
WHERE N < 12
)
SELECT LEFT(DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())),3) AS [month], 
       DATEPART(YEAR,DATEADD(MONTH,-N,GETDATE())) AS [year]
FROM R

Answer 2

declare @start DATE = '2011-10-01';


 with CTEE(date)
AS
(
    SELECT @start
    UNION   all

    SELECT DATEADD(month,-1,date)
    from CTEE
    where DATEADD(month,-1,date)>=DATEADD(month,-10,@start)
)
select Datename(month,date) from CTEE

Answer 3

SELECT STR(MONTH(DATEADD(mm, number, GETDATE())), 2) AS MonthNum, 
DATENAME(month, DATEADD(month, MONTH(DATEADD(mm, number, GETDATE())), 0) - 1) AS MonthNames
FROM master.dbo.spt_values
WHERE (name IS NULL) AND (number BETWEEN 0 AND 11)