SQL Server 逆透视/透视示例

Question

我正在申请管理酒店预订，我需要显示一年中每月的入住率。我做了一个查询，有点解决了问题，但我想以另一种格式呈现。

我当前的查询返回下表 (2x12):

January|February|March|April| ..... and so on
   20      15     18     20   ..... and so on

我想要这样的东西(12x2):

January|20
February|15
March|18
... |... 

这是我的查询:

Select  
        SUM(CASE WHEN datename(month, [CheckIn]) = 'January' or datename(month, [CheckOut]) = 'January' THEN 1 ELSE 0 END) January,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'February' or datename(month, [CheckOut]) = 'February' THEN 1 ELSE 0 END) February,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'March' or datename(month, [CheckOut]) = 'March' THEN 1 ELSE 0 END) March,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'April' or datename(month, [CheckOut]) = 'April' THEN 1 ELSE 0 END)  April,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'May' or datename(month, [CheckOut]) = 'May' THEN 1 ELSE 0 END)  May,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'June' or datename(month, [CheckOut]) = 'June' THEN 1 ELSE 0 END) June,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'July' or datename(month, [CheckOut]) = 'July' THEN 1 ELSE 0 END) July,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'August' or datename(month, [CheckOut]) = 'August' THEN 1 ELSE 0 END) August,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'September' or datename(month, [CheckOut]) = 'September' THEN 1 ELSE 0 END) September,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'October' or datename(month, [CheckOut]) = 'October' THEN 1 ELSE 0 END) October,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'November' or datename(month, [CheckOut]) = 'November' THEN 1 ELSE 0 END) November,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'December' or datename(month, [CheckOut]) = 'December' THEN 1 ELSE 0 END) December
FROM {Booking} INNER JOIN {Status} ON {Booking}.[StatusId] = {Status}.[Id]
WHERE {Booking}.[CheckIn] >= @BeginDate AND {Booking}.[CheckOut]  <= @EndDate AND {Status}.[Label] <> 'Canceled' 

任何帮助将不胜感激，我被困住了，网上没有那么多信息，谢谢!

Answer 1

看起来您正在尝试执行UNPIVOT:

SELECT Month, CheckIns
FROM
  (Select  
        SUM(CASE WHEN datename(month, [CheckIn]) = 'January' or datename(month, [CheckOut]) = 'January' THEN 1 ELSE 0 END) January,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'February' or datename(month, [CheckOut]) = 'February' THEN 1 ELSE 0 END) February,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'March' or datename(month, [CheckOut]) = 'March' THEN 1 ELSE 0 END) March,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'April' or datename(month, [CheckOut]) = 'April' THEN 1 ELSE 0 END)  April,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'May' or datename(month, [CheckOut]) = 'May' THEN 1 ELSE 0 END)  May,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'June' or datename(month, [CheckOut]) = 'June' THEN 1 ELSE 0 END) June,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'July' or datename(month, [CheckOut]) = 'July' THEN 1 ELSE 0 END) July,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'August' or datename(month, [CheckOut]) = 'August' THEN 1 ELSE 0 END) August,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'September' or datename(month, [CheckOut]) = 'September' THEN 1 ELSE 0 END) September,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'October' or datename(month, [CheckOut]) = 'October' THEN 1 ELSE 0 END) October,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'November' or datename(month, [CheckOut]) = 'November' THEN 1 ELSE 0 END) November,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'December' or datename(month, [CheckOut]) = 'December' THEN 1 ELSE 0 END) December
  FROM {Booking} INNER JOIN {Status} ON {Booking}.[StatusId] = {Status}.[Id]
  WHERE {Booking}.[CheckIn] >= @BeginDate AND {Booking}.[CheckOut]  <= @EndDate AND {Status}.[Label] <> 'Canceled'
) monthTotals
UNPIVOT
(CheckIns FOR Month IN
   (January, February, March, April, May, June, July, August, September, October, November, December)
) AS upvt

Here's a simplified SQLFiddle of this

和here's a doc page在 PIVOT 和 UNPIVOT

上