TSQL - Unpivot multiple columns

sql-server tsql pivot crosstab unpivot
SvenB picture SvenB · May 13, 2013 · Viewed 9.3k times · Source

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.

Answer

Martin Smith picture Martin Smith · May 13, 2013 
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

Related questions

Simple way to transpose columns and rows in SQL?

How do I simply switch columns with rows in SQL? Is there any simple command to transpose? ie turn this result: Paul | John | Tim | Eric Red 1 5 1 3 Green 8 4 3 5 Blue 2 2 9 1 into this: Red | Green | Blue Paul 1 8 2 John 5 4 2 Tim 1 3 9 Eric 3 5 1 PIVOT seems too …

Read More
SQL Server dynamic PIVOT query?

I've been tasked with coming up with a means of translating the following data: date category amount 1/1/2012 ABC 1000.00 2/1/2012 DEF 500.00 2/1/2012 GHI 800.00 2/10/2012 DEF 700.00 3/1/2012 ABC 1100.00 into the following: date ABC DEF GHI 1/1/2012 1000.00 2/1/2012 500.00 2/1/2012 800.00 2/10/2012 700.00 3/1/2012 1100.00 The blank spots can be NULLs or blanks, either is fine, …

Read More
Understanding PIVOT function in T-SQL

I am very new to SQL. I have a table like this: ID | TeamID | UserID | ElementID | PhaseID | Effort ----------------------------------------------------- 1 | 1 | 1 | 3 | 5 | 6.74 2 | 1 | 1 | 3 | 6 | 8.25 3 | 1 | 1 | 4 | 1 | 2.23 4 | 1 | 1 | 4 | 5 | 6.8 5 | 1 | 1 | 4 | 6 | 1.5 And I was told to get data like this ElementID | PhaseID1 | PhaseID5 | PhaseID6 -------------------------------------------- 3 | NULL | 6.74 | 8.25 4 | 2.23 | 6.8 | 1.5 I understand I need to use …

Read More