Unpivot the data based on certain conditions in SQL

Question

This is my table looks like

country     ID      weekyear    W1      W2      W3
------------------------------------------------------------
aa          1000    322015      0.00    4.50    0.00
bb          2000    262015      26.85   5.78    1.23
cc          3000    312015      0.94    0.00    2.90
dd          4000    362015      34.3    4.98    45.00

I have to unpivot the above table, but the challenge here is for example:

• For the ID = 1000, the WEEKYEAR says 322015 i.e. 32nd week of the year 2015. The very next value under W1 denotes WEEK 33 and the next value under W2 denotes WEEK 34 and so on.

• Similarly for the ID = 2000, WEEKYEAR says 26th week of 2015. So the next value under W1 denotes 27th WEEK and the next value under W2 is 28th week.

Kindly help unpivoting the table and the table looks exactly the same/

Answer 1

You can use CROSS APPLY to unpivot your table:

SQL Fiddle

SELECT
    t.country,
    t.ID,
    x.WeekYear,
    x.W
FROM tbl t
CROSS APPLY(VALUES
    (WeekYear + 10000, W1),
    (WeekYear + 20000, W2),
    (WeekYear + 30000, W3)
)x(WeekYear, W)

Another way is to use UNION ALL:

SQL Fiddle

SELECT
    country, ID, WeekYear + 10000 AS WeekYear, W1 AS W
FROM tbl
UNION ALL
SELECT
    country, ID, WeekYear + 20000 AS WeekYear, W2 AS W
FROM tbl
UNION ALL
SELECT
    country, ID, WeekYear + 30000 AS WeekYear, W3 AS W
FROM tbl
ORDER BY country, ID, WeekYear