How to Quiry Pivot 4 Tables

[kosaly]

Dear Sir/Madam

Could you help to give me an example how to quiry pivot 4 tables as below:

1. tblCity(Cityid, cityName) 
    1 Phnom Penh 
    2 Siem Reap 
    3 Sihanouk 

2. tblHotel(HotelID, HotelName) 
   1 City Angkor Hotel 
   2 Phnom Penh Hotel 
   3 Sihanouk Hotel 
   4 New York Hotel 
   5 Pacific Hotel 
   6 Angkor Star Hotel 
   7 Khemera Angkor Hotel 
   8 Sokha beach Hotel 
   9 Costle Hotel

3. tblTourClass(TourClassid, TourClass) 
   1 Deluxe Class 
   2 Supperies Class 
   3 Standard Class

4. tblTourHotel(id, CityID, HotelID, TourClassID) 
   1   1   2   1 
   2   2   1   1 
   3   3   3   1 
   4   1   4   2 
   5   1   5   3 
   6   2   6   2 
   7   2   7   3 
   8   3   8   2 
   9   3   9   3

and I want to display as below:

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

I'm waiting for your help.

many thanks
Kosal

[ck9663]

Join them first, then PIVOT the sub-query. You're going to need a key that link all these 4 tables. From your sample, there seems to be no key that links them together.

Good Luck!!!

~~ CK

[kosaly]

ok thank for you advise.

Now we can solve.

http://stackoverflow.com/questions/3200646/how-to-quiry-pivot-4-tables