What is the T-SQl syntax to transform data from a single table into this specific format?

Question

I am using SQL Server 2014 and I have a table in my database called MyTable. Extract of which is shown below:

Hotel   Market    Desc    Jan16      Feb16       
ABC     France    SUP HB   158        33       
ABC     France    SUP AI    35         6       
ABC     France    DLX HB    27        10       

I need to transform this table so that my SQL query output is as follows:

Hotel    Market   Desc      Date      Values
ABC      France   SUP HB    Jan16      158
ABC      France   SUP HB    Feb16       33
ABC      France   SUP AI    Jan16       35
ABC      France   SUP AI    Feb16        6
ABC      France   DLX HB    Jan16       27
ABC      France   DLX HB    Feb16       10

How can I achieve this output with T-SQL? After reading a bit on the problem on the web, I believe the UNPIVOT syntax might do the trick but I am at a total loss on how to use it in this context.

Answer 1

select  *
from    mytable unpivot ("values" for "date" in (Jan16,Feb16)) u

---

+-------+--------+--------+--------+-------+
| Hotel | Market | Desc   | values | date  |
+-------+--------+--------+--------+-------+
| ABC   | France | SUP HB | 158    | Jan16 |
+-------+--------+--------+--------+-------+
| ABC   | France | SUP HB | 33     | Feb16 |
+-------+--------+--------+--------+-------+
| ABC   | France | SUP AI | 35     | Jan16 |
+-------+--------+--------+--------+-------+
| ABC   | France | SUP AI | 6      | Feb16 |
+-------+--------+--------+--------+-------+
| ABC   | France | DLX HB | 27     | Jan16 |
+-------+--------+--------+--------+-------+
| ABC   | France | DLX HB | 10     | Feb16 |
+-------+--------+--------+--------+-------+