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


I don't have a high enough reputation score to comment on @PinnyM's excellent answer, but if you were looking for the name of the month you could modify it to do the following:

SELECT
  CSYEAR, 
  MONTH,
  DATENAME(Month, CAST(RIGHT(MONTH,2) AS VARCHAR(2)) + '/01/1900'), 
  NUMBER
FROM myTable
UNPIVOT
(
  NUMBER
  for MONTH in (CSOR01, CSOR02, CSOR03, CSOR04, CSOR05, CSOR06, CSOR07, CSOR08, CSOR09, CSOR10, CSOR11, CSOR12)
) u;

SQL Fiddle


Need Your Help

Shell script to get the process ID on Linux

linux bash shell scripting

I want to write a shell script (.sh file) to get a given process id. What I'm trying to do here is once I get the process ID, I want to kill that process. I'm running on Ubuntu (Linux).

React, ES6 compiling with Browserify, babel and gulp, issue importing components

reactjs gulp browserify babeljs

I'm playing with ES6, Gulp and Browserify, I'm on my first steps setting up my environment.