simple unpivot on 2 rows table

I have table that contains data of query, sample data looks like this:

| YEAR | JAN | FEB | ... | DEC |
|------|-----|-----|-----|-----|
| 2013 |  90 |  40 | ... |  50 |
| 2014 |  30 |  20 | ... |  40 |

I'm trying to unpivot this table to have data like this:

| MONTH | 2013 | 2014 |
|-------|------|------|
|  JAN  |  90  |  30  |
|  FEB  |  40  |  20  |
|  ...  |  ... |  ... |
|  DEC  |  50  |  40  |

I've tried this:

select Month, 2013, 2014
from Data
unpivot
(
  marks
  for Month in (Jan, Feb, Mar, Apr)
) u

but all I get are months and years. Here is my sqlfiddle

I will always have 12 months, but I can have multiple data rows.
Can this be done without dynamic sql?

Solution

You're not going to get what you want with a single unpivot statement. Start with this unpivot:

with cte(Year, Month, Orders)
as
(
  select Year, Month, Orders
  from Data d
  unpivot
  (
    orders
    for Month in (Jan, Feb, Mar, Apr)
  ) u
)

I'm going to use those results in the next part, so I store it as a CTE. This query gives you results like this:

| YEAR | MONTH | ORDERS |
|------|-------|--------|
| 2013 |  JAN  |  90    |
| 2013 |  FEB  |  40    |
| 2013 |  MAR  |  30    |
etc...

I don't know what the numbers in your table represent, but I just called them orders. You can rename that column to whatever is appropriate. The next step is to pivot those results so that we can get the year displayed as columns:

select Month, [2013], [2014]
from cte
pivot
(
  sum(orders)
  for year in ([2013], [2014])
) p
order by datepart(mm, Month+'1900')

If you need to add more years, it should be obvious where to do that. Note the clever order by that sorts the months chronologically instead of alphabetically.

Here's a SQL Fiddle.