4

How can I unpivot multiple columns in "one"?

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

See the screenshot please. enter image description here

At the top you see the input data. 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 table structure since the actual data contains more columns and more years to deal with.

Here's a Fiddle with the sample data.

Hope you can show me a way to get there. Thank you.

SvenB
  • 139
  • 3
  • 10

2 Answers2

9
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

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • thanks for both answers - I will go with the one that is easier to read and understand for me :-) – SvenB May 13 '13 at 14:05
  • Great post with support for SQL Fiddle. That helped me deviate from a dreaded implementation with a possible Frankenstein approach to UnPivot. – GoldBishop Oct 04 '16 at 16:08
2

One approach is to repivot after unpivoting - like so:

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.