0

I have data like below:

Year January Feb March April
2017  20     15  7     25

Is it possible to format the data to

Year Month  Value

 - 2017 January 20 
 - 2017 Feb     15 
 - 2017 March   7 
 - 2017 April   25

Thanks a lot for your help. I have been trying to achive this for hours but I am not able to.

Serkan Arslan
  • 13,158
  • 4
  • 29
  • 44
Agga
  • 285
  • 1
  • 2
  • 13
  • 1
    So [many](https://stackoverflow.com/questions/18617243/sql-unpivot-multiple-columns-data) [duplicates](https://stackoverflow.com/questions/19055902/unpivot-with-column-name). – Clockwork-Muse Oct 19 '17 at 18:37

2 Answers2

2

This is unpivot, but I prefer using apply:

select v.*
from t outer apply
     (values (t.year, 'January', t.January),
             (t.year, 'Feb', t.Feb),
             (t.year, 'March', t.March),
             (t.year, 'April', t.April)
     ) v(year, month, value);

apply implements something called a "lateral join". This is a lot like a correlated subquery that can return multiple columns. Lateral joins are very powerful -- unpivoting is just the beginning of what they do. But unpivoting is one way to start learning about them.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks Gordon, is there any reason why you like apply over unpivot – Agga Oct 19 '17 at 18:44
  • @Agga . . . I thought I explained that in the question. `apply` is a general purpose, powerful operator. `unpivot` exists just for one specific use case. – Gordon Linoff Oct 20 '17 at 00:58
1

You can use UNPIVOT for this.

DECLARE @T TABLE (Year INT, January INT, Feb INT, March INT, April INT)
INSERT INTO @T VALUES(2017,20,15,7,25)

SELECT [Year], [Month], [Value] FROM @T 
    UNPIVOT( [Value] FOR [Month] IN( [January], [Feb], [March], [April] ) ) AS UNPVT

Result

Year        Month       Value
----------- ----------- -----------
2017        January     20
2017        Feb         15
2017        March       7
2017        April       25
Serkan Arslan
  • 13,158
  • 4
  • 29
  • 44