Let's assume that you're interested in a rolling 13 months and you are not tied to the column names you have specified. You can do this by a combination of the CROSS APPLY VALUES approach to UNPIVOT (http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/) and a crosstab, like this:

DECLARE @StartMonth DATETIME = '2011-07-01'

SELECT val

,M01=MAX(CASE WHEN FixDate = DATEADD(m, 0, @StartMonth) THEN value ELSE NULL END)

,M02=MAX(CASE WHEN FixDate = DATEADD(m, 1, @StartMonth) THEN value ELSE NULL END)

,M03=MAX(CASE WHEN FixDate = DATEADD(m, 2, @StartMonth) THEN value ELSE NULL END)

,M04=MAX(CASE WHEN FixDate = DATEADD(m, 3, @StartMonth) THEN value ELSE NULL END)

,M05=MAX(CASE WHEN FixDate = DATEADD(m, 4, @StartMonth) THEN value ELSE NULL END)

,M06=MAX(CASE WHEN FixDate = DATEADD(m, 5, @StartMonth) THEN value ELSE NULL END)

,M07=MAX(CASE WHEN FixDate = DATEADD(m, 6, @StartMonth) THEN value ELSE NULL END)

,M08=MAX(CASE WHEN FixDate = DATEADD(m, 7, @StartMonth) THEN value ELSE NULL END)

,M09=MAX(CASE WHEN FixDate = DATEADD(m, 8, @StartMonth) THEN value ELSE NULL END)

,M10=MAX(CASE WHEN FixDate = DATEADD(m, 9, @StartMonth) THEN value ELSE NULL END)

,M11=MAX(CASE WHEN FixDate = DATEADD(m, 10, @StartMonth) THEN value ELSE NULL END)

,M12=MAX(CASE WHEN FixDate = DATEADD(m, 11, @StartMonth) THEN value ELSE NULL END)

,M13=MAX(CASE WHEN FixDate = DATEADD(m, 12, @StartMonth) THEN value ELSE NULL END)

FROM #Test

CROSS APPLY (SELECT DATEADD(month, DATEDIFF(month, 0, Date), 0)) a (FixDate)

CROSS APPLY (

VALUES ('val1', val1)

,('val2', val2)

,('val3', val3)

,('val4', val4)) b (val, value)

GROUP BY val

The CROSS APPLY to create "FixDate" may not be necessary if your data is already truncated to the first of the month.

If you must have the column names as you've specified, you can dump the above into another temporary table:

DECLARE @StartMonth DATETIME = '2011-07-01'

,@SQL VARCHAR(MAX)

CREATE TABLE #R13MO

(

Val VARCHAR(10),

M01 INT,

M02 INT,

M03 INT,

M04 INT,

M05 INT,

M06 INT,

M07 INT,

M08 INT,

M09 INT,

M10 INT,

M11 INT,

M12 INT,

M13 INT

)

INSERT INTO #R13MO

SELECT val

,M01=MAX(CASE WHEN FixDate = DATEADD(m, 0, @StartMonth) THEN value ELSE NULL END)

,M02=MAX(CASE WHEN FixDate = DATEADD(m, 1, @StartMonth) THEN value ELSE NULL END)

,M03=MAX(CASE WHEN FixDate = DATEADD(m, 2, @StartMonth) THEN value ELSE NULL END)

,M04=MAX(CASE WHEN FixDate = DATEADD(m, 3, @StartMonth) THEN value ELSE NULL END)

,M05=MAX(CASE WHEN FixDate = DATEADD(m, 4, @StartMonth) THEN value ELSE NULL END)

,M06=MAX(CASE WHEN FixDate = DATEADD(m, 5, @StartMonth) THEN value ELSE NULL END)

,M07=MAX(CASE WHEN FixDate = DATEADD(m, 6, @StartMonth) THEN value ELSE NULL END)

,M08=MAX(CASE WHEN FixDate = DATEADD(m, 7, @StartMonth) THEN value ELSE NULL END)

,M09=MAX(CASE WHEN FixDate = DATEADD(m, 8, @StartMonth) THEN value ELSE NULL END)

,M10=MAX(CASE WHEN FixDate = DATEADD(m, 9, @StartMonth) THEN value ELSE NULL END)

,M11=MAX(CASE WHEN FixDate = DATEADD(m, 10, @StartMonth) THEN value ELSE NULL END)

,M12=MAX(CASE WHEN FixDate = DATEADD(m, 11, @StartMonth) THEN value ELSE NULL END)

,M13=MAX(CASE WHEN FixDate = DATEADD(m, 12, @StartMonth) THEN value ELSE NULL END)

FROM #Test

CROSS APPLY (SELECT DATEADD(month, DATEDIFF(month, 0, Date), 0)) a (FixDate)

CROSS APPLY (

VALUES ('val1', val1)

,('val2', val2)

,('val3', val3)

,('val4', val4)) b (val, value)

GROUP BY val

And then do a (relatively) simple dynamic SQL like this:

;WITH Tally (n) AS (

SELECT TOP 13 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1

FROM sys.all_columns)

SELECT @SQL = 'SELECT val' +

(

SELECT ',[' + REPLACE(CONVERT(VARCHAR(10), Date, 101), '/', '-') + ']=M' +

RIGHT('00' + CAST(MONTH(Date) AS VARCHAR), 2)

FROM (SELECT DATEADD(m, n, @StartMonth) FROM Tally) a(Date)

FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)') + ' FROM #R13MO'

PRINT @SQL

EXEC (@SQL)

Or, you could also construct and execute the dynamic SQL to create #R13MO temp table and then execute a simple static DML query from that table.