TSQL - Multiple Unpivot Columns

Question

How to disconnect several columns in "one"?

Right now I have a univot for each column, but this creates a lot of empty rows.

See the screenshot, please.

At the top you see the input. At the moment, I'm at the table in the middle with this code:

SELECT [ID], [RowNumber], [Year], [Sales] FROM ( SELECT ID, RowNumber, [Sales 2013] as [2013], [Sales 2014] as [2014] FROM mytable) p UNPIVOT ( [Sales] FOR [Year] IN ([2013], [2014]) )AS unpvt ; 

But I think it would be much better to get to the bottom structure of the table, as the actual data contains more columns and more years to solve.

Here is a script with sample data.

I hope you can show me the way to get there. Thanks.

Answer 1

 SELECT [ID], [RowNumber], [Year], Sales, Budget FROM mytable CROSS APPLY (VALUES (2013, [Sales 2013], [Budget 2013]), (2014, [Sales 2014], [Budget 2014]) ) V([Year], Sales, Budget) 

SQL Fiddle

Answer 2

One approach is to replicate after a U-turn - for example:

 select [Id], [Year], [Sales], [Budget], [Actual] from (SELECT [Id], Left([Colhead], charindex(' ',[Colhead])-1) [Category], Right([Colhead], len([Colhead])-charindex(' ',[Colhead])) [Year], [Figures] FROM (SELECT * FROM mytable) p UNPIVOT ([Figures] FOR [Colhead] IN ([Sales 2013],[Sales 2014],[Budget 2013],[Budget 2014],[Actual 2013],[Actual 2014]) ) AS unpvt) as u pivot (max([Figures]) for [Category] in ([Sales], [Budget], [Actual])) as p 

SQLFiddle here .