SQL Pivot Query для извлечения данных из таблицы VIEW

Question

Я использую SQL Server 2014, и у меня есть следующий запрос (который я преобразовал в таблицу VIEW):

SELECT 
  b.PropertyCode,
  c.PMSConfirmationNumber,
  x.[ReservationStayID],
  a.FirstName + ' ' + a.LastName AS 'Name',
  b.ReservationStatus AS 'Status',
  d.rsl_nationality AS 'Nationality',
  d.rsl_rateplan AS 'Rate Plan Code',
  d.rsl_roomtype AS 'Room Type',
  d.rsl_mealplan AS 'Meal Plan',
  b.GuestCount AS 'Total Guest',
  x.[Nights Spent] AS 'Room Nights',
  x.[MTH],
  x.[Rate] AS 'Room Rate WITH VAT',
  c.CurrencyCode, 
  h.ROE AS 'Rate of Exchange', 
  (x.[Nights Spent]*x.[Rate]*h.[ROE])/NULLIF(1.15,0) AS 'PkgRevenue, Excl. VAT', 
  ((x.[Nights Spent]*x.[Rate]*h.[ROE])/1.15)/NULLIF((b.GuestCount*x.[Nights Spent]),0)     AS 'GADR, Excl. VAT', 
  x.CreatedOn,
  x.[DateOfArrival],
  x.[DateOfDeparture],

  e.TravelAgencyTypeCode AS 'Source of Business', 
  c.TAProfileID, 
  e.Name AS 'Tour Operator', 
  g.CountryGroup AS 'Market', 
  c.TAProfileID2, 
  e2.Name AS 'Booking Origin (1)', 
  g2.CountryGroup AS 'Booking Origin (2)',

 FROM GuestNameInfo a
 JOIN GuestStaySummary b ON a.ReservationStayID = b.ReservationStayID
 LEFT JOIN ReservationStay c ON c.ReservationStayID = b.ReservationStayID
 LEFT JOIN P5RESERVATIONLIST d ON d.rsl_code = b.ReservationStayID 
 LEFT JOIN TravelAgency e ON e.TravelAgencyID = c.TAProfileID
 LEFT JOIN Market g ON e.CountryCode = g.CountryCode 
 LEFT JOIN TravelAgency e2 ON e2.TravelAgencyID = c.TAProfileID2
 LEFT JOIN Market g2 ON e2.CountryCode = g2.CountryCode 
 LEFT JOIN Exrate h ON h.Ccode = c.CurrencyCode 

 LEFT JOIN
 (
   SELECT 
     ReservationStayID,
     datename(m,StayDate) + ' ' + cast(datepart(yyyy,StayDate) as varchar) as [MTH],
     count(*) AS [Nights Spent],
     avg(RateAmount) as [Rate],
     min(CreatedOn) as CreatedOn,
     min(StayDate) as [DateOfArrival],
     max(StayDate) as [DateOfDeparture]
   FROM ReservationStayDate
   GROUP BY ReservationStayID, datename(m,StayDate) + ' ' + cast(datepart(yyyy,StayDate) as varchar)
 ) x ON x.ReservationStayID = b.ReservationStayID

 WHERE a.PrimaryGuest = '+' AND d.rsl_primaryguest = '+'

Теперь мне нужен сводный запрос, который будет работать с вышеуказанной таблицей просмотра. Я написал следующий запрос (показанный ниже), но мне кажется, что я что-то упустил, так как я получаю следующую ошибку при запуске кода: "Msg 207, уровень 16, состояние 1, строка 4 Неверное имя столбца" MTH "".

SELECT [Tour Operator],[MTH]

FROM HOLDINGS

PIVOT (SUM([ROOM NIGHTS])

FOR [MTH] IN ([NOVEMBER 2014],[DECEMBER 2014],[JANUARY 2015]))

AS PVTTABLE

Answer 1

Это не правильный синтаксис pivot и убедитесь, что ваше имя зовут HOLDINGS,

Синтаксис Pivot

SELECT <non-pivoted COLUMN>,
    [first pivoted column] AS <COLUMN name>,
    [second pivoted column] AS <COLUMN name>,
    ...
    [last pivoted column] AS <COLUMN name>
FROM
    (<SELECT query that produces the data>)
    AS <alias FOR the source query>
PIVOT
(
    <aggregation FUNCTION>(<COLUMN being aggregated>)
FOR
[<column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column],
    ... [last pivoted column])
) AS <alias FOR the PIVOT TABLE>
<optional ORDER BY clause>;

Ваш Pivot запрос должен быть примерно таким.

SELECT *
FROM   (SELECT [Tour Operator],
               [MTH],
               [ROOM NIGHTS]
        FROM   HOLDINGS) a
       PIVOT (Sum([ROOM NIGHTS])
             FOR [MTH] IN ([NOVEMBER 2014],
                           [DECEMBER 2014],
                           [JANUARY 2015])) AS PVTTABLE