How to Query 4 Pivot Tables

Could you help me to check as below:

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

Here's the actual query:

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

And the results:

 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

But I want to display as bellow:

 City Name   Deluxe Class      Superior           Standard 
 Siem Reap   CityAngkor Hotel  Angkor Star Hotel  Khemera Angkor Hotel 
 Phnom Penh  Phnom Penh开发者_Python百科 Hotel  New York Hotel     Pacific Hotel  
 Sihanouk    Sihanouk Hotel    Sokha beach Hotel  Costle Hotel

Could you help me to do how to display as above.

many thanks

Try this:

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

It looks like the by using SELECT tc.*, h.*, c.*, the computation of the aggregate value became a problem. But I also rearranged the query so that @tblCity is the basis for all the LEFT JOINS.