Unconventional Unpivot and Join

I have looked around and I have not found anything exactly like this so I thought it might be helpful to everyone to ask. The long story short is that I have a column with a year followed by columns of months which contain numbers:

[year] | [month1] | [month2]
1999       4          2

Basically, I want to unpivot the month so I can name it something normal like "March" or "03" while keeping the year to the left and the number to the right:

[year] | [month] | [numbers]
1999      month1      4
1999      month2      2

Finally, I want to take that into Report Builder but I will leave that for later. Here is a copy of the data with which I am working.

I really hope this is descriptive enough because this have been bugging me for a couple of days.

CSYEAR  CSOR01  CSOR02  CSOR03  CSOR04  CSOR05  CSOR06  CSOR07  CSOR08  CSOR09  CSOR10  CSOR11  CSOR12
1999        2       0       0       0       1       2       0       3       1       4       0       3
2000        4       1       3       3       2       2       2       2       4       1       4       4

1999    CSOR01  2                                       
1999    CSOR02  0                                       
1999    CSOR03  0                                       
1999    CSOR04  0                                       
1999    CSOR05  1                                       
1999    CSOR06  2                                       
1999    CSOR07  0                                       
1999    CSOR08  3                                       
1999    CSOR09  1                                       
1999    CSOR10  4                                       
1999    CSOR11  0                                       
1999    CSOR12  3                                       
2000    CSOR01  4                                       
2000    CSOR02  1                                       
2000    CSOR03  3                                       
2000    CSOR04  3                                       
2000    CSOR05  2                                       
2000    CSOR06  2                                       
2000    CSOR07  2                                       
2000    CSOR08  2                                       
2000    CSOR09  4                                       
2000    CSOR10  1                                       
2000    CSOR11  4                                       
2000    CSOR12  4                                       

Thank you all for any help you can recommend.

Answers


SELECT CSYEAR, MONTH, NUMBER
FROM myTable
UNPIVOT
(
  NUMBER
  for MONTH in (CSOR01, CSOR02, CSOR03, CSOR04, CSOR05, CSOR06, CSOR07, CSOR08, CSOR09, CSOR10, CSOR11, CSOR12)
) u;

SQLFiddle here


Need Your Help

Find IP address of directly connected device

windows ip discovery

Is there a way to find out the IP address of a device that is directly connected to a specific ethernet interface? I.e. given one host, one wired ethernet connection and one second host connected t...

How to replace a character programatically in Oracle 8.x series

java sql oracle exception unicode

Due to repetitive errors with one of our Java applications:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.