将数据从单个表转换为这种特定格式的table语法是什么？

Question

我正在使用Server 2014，我的数据库中有一个名为MyTable的表。其摘录如下：

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

我需要转换这个表，以便我的SQL查询输出如下：

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

如何使用T实现此输出？在阅读了一些关于网络上的问题之后，我相信UNPIVOT的语法可能会起作用，但我对如何在这种情况下使用它完全不知所措。

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 |
+-------+--------+--------+--------+-------+

Answer 2

select  Hotel  ,  Market,[desc],marks,Date 
from #c s
unpivot
(
  marks
  for date in (jan16, feb16)
) u;

或2)

SELECT  HOTEL ,MARKET,A.[DESC] MARKET,[DATE] ,V.[DESC]
FROM   #C A
       CROSS APPLY (VALUES('JAN16', JAN16),
                          ('FEB16', FEB16))
                       V   (DATE, [DESC]); 
output

Hotel   Market  desc    marks   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

Answer 3

一个简单的方法是使用UNION

SELECT Hotel, Market, Desc, 'Jan16' AS Date, Jan16 AS Values
FROM MyTable
UNION ALL
SELECT Hotel, Market, Desc, 'Feb16', Feb16
FROM MyTable

如果您期望得到比这更复杂的东西，例如超过两列，那么您可以尝试使用UNPIVOT。