I'm not sure if this is the best way, given you have a fairly small fixed data set, but it's an excuse for a dynamic pivot.

First get all the column names you need like this. They're not your exact column names, but you get the idea

DECLARE @columns varchar(max);

SELECT @columns = COALESCE (

@columns + ',[' + cast(DATEPART(yyyy, Date) as varchar) + '_' + cast(DATEPART(mm, Date) as varchar) + ']',

'[' + cast(DATEPART(yyyy, Date) as varchar)+ '_' + cast(DATEPART(mm, Date) as varchar) + ']'

)

FROM #Test

GROUP BY

DATEPART(yyyy, Date),DATEPART(MM, Date)

ORDER BY DATEPART(yyyy, Date),DATEPART(MM, Date);

SELECT @columns;

Next get a dynamic pivot on just one VALx, in this case VAL1

DECLARE @SQL VARCHAR(MAX)

SET @SQL = 'SELECT *

FROM ( SELECT ''Val1'' AS VALUE,

cast(DATEPART(yyyy, Date) as varchar) + ''_'' + cast(DATEPART(mm, Date) as varchar) AS DateMonth,

Val1

FROM #Test ) P

PIVOT (

MAX(Val1) FOR DateMonth in (' + @columns + ')

) AS PVT';

PRINT @SQL

That will give you the Val1 row only.

As you only have VAL1 to VAL4 - its probably easier to just UNION this with 3 copies of itself, replacing VAL1 with VAL2, VAL3, VAL4. But its possible to continue ... with help from a tally table ... Replace the above dynamic pivot with this one to do it for you.

DECLARE @SQL VARCHAR(MAX);

SELECT @SQL = COALESCE ( @SQL + ' SELECT *

FROM ( SELECT ''Val' + CAST(N-1 AS VARCHAR) + ''' AS VALUE,

cast(DATEPART(yyyy, Date) as varchar) + ''_'' + cast(DATEPART(mm, Date) as varchar) AS DateMonth,

Val' + CAST(N-1 AS VARCHAR) + '

FROM #Test ) P

PIVOT (

MAX(Val' + CAST(N-1 AS VARCHAR) + ') FOR DateMonth in (' + @columns + ')

) AS PVT '

+ CASE N WHEN 5 THEN '' ELSE ' UNION ' END

, '' )

FROM dbo.Tally AS N

WHERE N < 6;

PRINT @SQL

EXEC (@SQL)