如何查询 4 ​​个数据透视表

Question

你能帮我检查如下：

DECLARE @tblCity TABLE (CityID INT, CityName VARCHAR(100))  
DECLARE @tblHotel TABLE (HotelID INT, HotelName VARCHAR(100)) 
DECLARE @tblTourClass TABLE (TourClassID INT, TourClass VARCHAR(100)) 
DECLARE @tblTourHotel TABLE (id INT, CityID INT, HotelID INT, TourClassID INT) 

INSERT INTO @tblCity SELECT 1, 'Phnom Penh' UNION SELECT 2, 'Siem Reap' UNION SELECT 3, 'Sihanouk Vill'
INSERT INTO @tblHotel SELECT 1, 'City Angkor Hotel' UNION SELECT 2, 'Phnom Penh Hotel' UNION SELECT 3, 'Sihanouk Ville Hotel' UNION SELECT 4, 'New York Hotel' UNION SELECT 5, 'Pacific Hotel' UNION SELECT 6, 'Angkor Star Hotel' UNION SELECT 7, 'Khemera Angkor Hotel' UNION SELECT 8, 'Sokha beach Hotel' UNION SELECT 9, 'Costle Hotel'
INSERT INTO @tblTourClass SELECT 1, 'Deluxe Class' UNION SELECT 2, 'Superior Class' UNION SELECT 3, 'Standard Class'
INSERT INTO @tblTourHotel SELECT 1, 1, 2, 1 UNION SELECT 2, 2, 1, 1 UNION SELECT 3,3,3,1 UNION SELECT 4,1,4,2 UNION SELECT 5,1,5,3 UNION SELECT 6,2,6,2 UNION SELECT 7,2,7,3 UNION SELECT 8,3,8,2 UNION SELECT 9,3,9,3

这是实际的查询：

SELECT CityName, [Deluxe Class], [Superior Class], [Standard Class]
FROM
(
SELECT tc.*, h.*, c.* 
FROM @tblTourClass tc 
    LEFT JOIN @tblTourHotel th ON tc.TourClassID = th.TourClassID 
    LEFT JOIN @tblCity c ON th.CityID = c.CityID 
    LEFT JOIN @tblHotel h ON th.HotelID = h.HotelID
) AS sourcetable
PIVOT 
(   MAX(HotelName) 
  FOR TourClass IN ([Deluxe Class], [Superior Class], [Standard Class]) 
) AS pivottable
WHERE CityName IS NOT NULL

和结果：

 City Name       Deluxe Class            Superior    First   Standard
 Siem Reap       City Angkor Hotel       NULL    NULL    NULL
 Phnom Penh      Phnom Penh Hotel        NULL    NULL    NULL
 Sihanouk Vill   Sihanouk Ville Hotel    NULL    NULL    NULL

但我想显示如下：

 City Name   Deluxe Class      Superior           Standard 
 Siem Reap   CityAngkor Hotel  Angkor Star Hotel  Khemera Angkor Hotel 
 Phnom Penh  Phnom Penh Hotel  New York Hotel     Pacific Hotel  
 Sihanouk    Sihanouk Hotel    Sokha beach Hotel  Costle Hotel

你能帮我做如何显示如上。

非常感谢

Answer 1

试试这个：

SELECT
  pvt.CityName,
  pvt.[Deluxe Class],
  pvt.[Superior Class],
  pvt.[Standard Class]
FROM ( 
SELECT
  c
  .CityName,
  h.HotelName,
  tc.TourClass
FROM @tblCity c
LEFT JOIN @tblTourHotel th ON c.CityID = th.CityID
LEFT JOIN @tblHotel h ON th.HotelID = h.HotelID
LEFT JOIN @tblTourClass tc ON th.TourClassID = tc.TourClassID
) t
PIVOT (
  MAX(HotelName)
  FOR TourClass IN ([Deluxe Class], [Superior Class], [Standard Class])
) AS pvt

看起来通过使用SELECT tc.*, h.*, c.*，聚合值的计算就成了问题。但我还重新排列了查询，以便 @tblCity 成为所有 LEFT JOINS 的基础。