How to Quiry Pivot 4 Tables

Question

Dear Sir/Madam

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

tblCity(Cityid, cityName)

    1 Phnom Penh
    2 Siem Reap
    3 Sihanouk Vill

tblHotel(HotelID, HotelName)

    1  City Angkor Hotel
    2  Phnom Penh Hotel
    3  Sihanouk Ville Hotel

tblTourClass(TourClassid, TourClass)

    1    Deluxe Class
    2    Fisrt Class
    3    Supperies Class
    4    Standard Class

tblTourHotel(id, CityID, HotelID, TourClassID)
```
    1   1  2   1
    2   2  1   1
    3   3  3   1
```
and I want to display as below:
-----------------------------------------------------------------------------------------------
CityName | Deluxe Class | Fisrt Class | Supperies Class | Standard Class |
Phnom Pemh | Phnom Penh Hotel | | | |
Siem Reap | City Angkor Hotel | | | |
SihanoukVille | Sihanouk Ville Hotel | | | |

I'm waiting for your help.

many thanks Kosal

Answer 1

Alter PROCEDURE  uspTmpPivotTableTry 
as
Begin
SET NOCOUNT ON
CREATE TABLE #MasterTbl
             (
              RowText VARCHAR(50),
              TourClassid  int,
           TourClass  Varchar(100),   
           HotelId int
             )
INSERT INTO #MasterTbl
            (
             RowText,
             TourClassid,
            TourClass,
          HotelId
            )
SELECT      tblCity.Cityname,
            tblTourHotel.TourClassId,
         tblTourClass.TourClass,
         tblTourHotel.HotelId
FROM        tblTourHotel, tblCity ,tblTourClass
         Where tblTourHotel.CityId =tblCity.CityId  and tblTourClass.TourClassId = tblTourHotel.TourClassId

CREATE UNIQUE INDEX IX_MasterRef ON #MasterTbl (RowText, TourClassid,TourClass,HotelId)

CREATE TABLE #ColumnsTbl
            (
             ColumnIndex INT IDENTITY (0, 1),
             ColumnText VARCHAR(50)
             )

INSERT INTO     #ColumnsTbl
                (
                 ColumnText
                )
SELECT DISTINCT TourClass
FROM           tblTourClass
ORDER BY       TourClass

CREATE UNIQUE INDEX IX_Columns ON #ColumnsTbl (ColumnIndex, ColumnText)

CREATE TABLE #RowsTbl 
             (
              CityName VARCHAR(100),
           TourClassid  int,
           HotelId int
             )
 INSERT INTO    #RowsTbl
                (
                 CityName,TourClassid, HotelId
                )
SELECT tblCity.CityName,tblTourHotel.TourClassid, tblTourHotel.HotelId from tblCity, tblTourHotel where tblTourHotel.CityId =tblCity.CityId

CREATE UNIQUE INDEX IX_Rows ON #RowsTbl (CityName,TourClassid, HotelId)

DECLARE     @ColumnIndex INT,
            @MaxColumnIndex INT,
            @ColumnText VARCHAR(50),
            @SQLstr VARCHAR(1000)

SELECT      @ColumnIndex = 0, @MaxColumnIndex = MAX(ColumnIndex) FROM  #ColumnsTbl
declare  @SelectQuery VARCHAR(2000)
Set @SelectQuery = 'Select CityName ,'
WHILE @ColumnIndex <= @MaxColumnIndex
   BEGIN
      SELECT     @ColumnText = ColumnText
      FROM       #ColumnsTbl
      WHERE      ColumnIndex = @ColumnIndex

      SELECT     @SQLstr = 'ALTER TABLE #RowsTbl ADD ' + QUOTENAME(@ColumnText) + ' VARCHAR(100) NULL DEFAULT '''''
      EXEC       (@SQLstr)

      SELECT     @SQLstr = 'UPDATE #RowsTbl SET ' + QUOTENAME(@ColumnText) + ' = (Select A.HotelName 
                 from tblHotel A Where A.HotelId = #MasterTbl.HotelId)
                         FROM #MasterTbl , #ColumnsTbl
                     WHERE #RowsTbl.HotelId = #MasterTbl.HotelId 
                    and #RowsTbl.TourClassId = #MasterTbl.TourClassId 
                     AND #ColumnsTbl.ColumnText  = #MasterTbl.TourClass
                     AND #ColumnsTbl.ColumnIndex = ' + CAST(@ColumnIndex AS VARCHAR(12))
      EXEC       (@SQLstr)
       Set @SelectQuery = @SelectQuery + 'MAX(' + QUOTENAME(@ColumnText) + ') as ' + QUOTENAME(@ColumnText) + ','
      SELECT  @ColumnIndex = @ColumnIndex + 1 

   END
    Set @SelectQuery = Left(@SelectQuery ,Len(@SelectQuery )-1)
    Set @SelectQuery = @SelectQuery + ' from #RowsTbl group by CityName'
      EXEC       (@SelectQuery)

DROP TABLE #ColumnsTbl
DROP TABLE #MasterTbl
DROP TABLE  #RowsTbl
End

End of Procedure

TABLES AND SAMPLE DATA Which I hav used

create table tblCity
    (Cityid int identity (1,1), cityName Varchar(100))

create table tblHotel
    (HotelID int identity (1,1), HotelName Varchar(100))

create table   tblTourClass
    (TourClassid int identity(1,1), TourClass Varchar(100))

create table tblTourHotel
    (id int identity (1,1), CityID int, HotelID int, TourClassID int)


insert into tblCity (cityName) 
    select 'Phnom Penh' union all 
    select 'Siem Reap' union all 
    select 'Sihanouk '

insert into tblHotel (HotelName)
    select 'City Angkor Hotel' union all 
    select 'Phnom Penh Hotel' union all
    select 'Sihanouk Hotel' union all
    select 'New York Hotel' union all 
    select 'Pacific Hotel' union all 
    select 'Angkor Star Hotel' union all
    select 'Khemera Angkor Hotel' union all
    select 'Sokha beach Hotel' union all 
    select 'Costle Hotel'

insert into tblTourClass (TourClass)
    select 'Deluxe Class' union all 
    select 'Supperies Class' union all 
    select 'Standard Class'

insert into tblTourHotel( CityID,HotelID,TourClassID) 
    select 1, 2, 1 union all 
    select 2, 1, 1 union all 
    select 3, 3 ,1 union all 
    select 1, 4, 2 union all 
    select 1, 5 ,3 union all 
    select 2, 6, 2 union all 
    select 2, 7, 3 union all 
    select 3, 8, 2 union all 
    select 3, 9, 3 

 Exec uspTmpPivotTableTry

---Output will be

CityName          Deluxe Class        Standard Class         Supperies Class
--------------------------------------------------------------------------------
Phnom Penh        Phnom Penh Hotel    Pacific Hotel          New York Hotel
Siem Reap         City Angkor Hotel   Khemera Angkor Hotel   Angkor Star Hotel
Sihanouk          Sihanouk Hotel      Costle Hotel           Sokha beach Hotel

For : Kosal Please format properly while u r framing a question , it was quite difficult to read and understand. I hav done the changes by assuming that this is wat u need..

Answer 2

How's this?

This first chunk is just setting up some temporary tables to show the concept:

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'
INSERT INTO @tblTourClass SELECT 1, 'Deluxe Class' UNION SELECT 2, 'First Class' UNION SELECT 3, 'Superior Class' UNION SELECT 4, 'Standard Class'
INSERT INTO @tblTourHotel SELECT 1, 1, 2, 1 UNION SELECT 2, 2, 1, 1 UNION SELECT 3,3,3,1

Here's the actual query:

SELECT CityName, [Deluxe Class], [Superior Class], [First 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], [First 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

Answer 3

create table tblCity  (Cityid int identity (1,1), cityName Varchar(100))
create table tblHotel (HotelID int identity (1,1), HotelName Varchar(100))
create table tblTourClass (TourClassid int identity(1,1), TourClass Varchar(100))
create table tblTourHotel (id int identity (1,1), CityID int, HotelID int, TourClassID int)
insert into tblCity (cityName) 
        select 'Phnom Penh' union all 
        select 'Siem Reap' union all 
        select 'Sihanouk Vill'
insert into tblHotel (HotelName)
     select 'City Angkor Hotel' union all 
         select 'Phnom Penh Hotel' union all
         select 'Sihanouk Ville Hotel'
insert into tblTourClass (TourClass)
      select  'Deluxe Class' union all 
      select   'Fisrt Class' union all 
      select   'Supperies Class' union all 
      select   'Standard Class'

insert into tblTourHotel( CityID,HotelID,TourClassID) select 1,1,3 union all select 2,1,1 
union all select 3,3,1

create table tblCity  (Cityid int identity (1,1), cityName Varchar(100))
create table tblHotel (HotelID int identity (1,1), HotelName Varchar(100))
create table tblTourClass (TourClassid int identity(1,1), TourClass Varchar(100))
create table tblTourHotel (id int identity (1,1), CityID int, HotelID int, TourClassID int)
insert into tblCity (cityName) 
        select 'Phnom Penh' union all 
        select 'Siem Reap' union all 
        select 'Sihanouk Vill'
insert into tblHotel (HotelName)
     select 'City Angkor Hotel' union all 
         select 'Phnom Penh Hotel' union all
         select 'Sihanouk Ville Hotel'
insert into tblTourClass (TourClass)
      select  'Deluxe Class' union all 
      select   'Fisrt Class' union all 
      select   'Supperies Class' union all 
      select   'Standard Class'

insert into tblTourHotel( CityID,HotelID,TourClassID) select 1,1,3 union all select 2,1,1 
union all select 3,3,1

--- NOW CREATE PROCEDURE

Create PROCEDURE   uspTmpPivotTableTry 
as
Begin
SET NOCOUNT ON
CREATE TABLE #MasterTbl
             (
              RowText VARCHAR(50),
              TourClassid  int,
           TourClass  Varchar(100),   
           HotelId int
             )
INSERT INTO #MasterTbl
            (
             RowText,
             TourClassid,
            TourClass,
          HotelId
            )
SELECT      tblCity.Cityname,
            tblTourHotel.TourClassId,
         tblTourClass.TourClass,
         tblTourHotel.HotelId
FROM        tblTourHotel, tblCity ,tblTourClass
         Where tblTourHotel.CityId =tblCity.CityId  and tblTourClass.TourClassId = tblTourHotel.TourClassId

CREATE UNIQUE INDEX IX_MasterRef ON #MasterTbl (RowText, TourClassid,TourClass,HotelId)

CREATE TABLE #ColumnsTbl
            (
             ColumnIndex INT IDENTITY (0, 1),
             ColumnText VARCHAR(50)
             )

INSERT INTO     #ColumnsTbl
                (
                 ColumnText
                )
SELECT DISTINCT TourClass
FROM           tblTourClass
ORDER BY       TourClass

CREATE UNIQUE INDEX IX_Columns ON #ColumnsTbl (ColumnIndex, ColumnText)

CREATE TABLE #RowsTbl 
             (
              CityName VARCHAR(100),
           TourClassid  int,
           HotelId int
             )
 INSERT INTO    #RowsTbl
                (
                 CityName,TourClassid, HotelId
                )
SELECT tblCity.CityName,tblTourHotel.TourClassid, tblTourHotel.HotelId from tblCity, tblTourHotel where tblTourHotel.CityId =tblCity.CityId

CREATE UNIQUE INDEX IX_Rows ON #RowsTbl (CityName,TourClassid, HotelId)

DECLARE     @ColumnIndex INT,
            @MaxColumnIndex INT,
            @ColumnText VARCHAR(50),
            @SQLstr VARCHAR(1000)

SELECT      @ColumnIndex = 0, @MaxColumnIndex = MAX(ColumnIndex) FROM  #ColumnsTbl


WHILE @ColumnIndex <= @MaxColumnIndex
   BEGIN
      SELECT     @ColumnText = ColumnText
      FROM       #ColumnsTbl
      WHERE      ColumnIndex = @ColumnIndex

      SELECT     @SQLstr = 'ALTER TABLE #RowsTbl ADD ' + QUOTENAME(@ColumnText) + ' VARCHAR(100) NULL DEFAULT '''''
      EXEC       (@SQLstr)

      SELECT     @SQLstr = 'UPDATE #RowsTbl SET ' + QUOTENAME(@ColumnText) + ' = (Select A.HotelName 
                 from tblHotel A Where A.HotelId = #MasterTbl.HotelId)
                         FROM #MasterTbl (INDEX(IX_MasterRef), NOLOCK)
                        , #ColumnsTbl (INDEX(IX_Columns), NOLOCK) 
                     WHERE #RowsTbl.HotelId = #MasterTbl.HotelId 
                    and #RowsTbl.TourClassId = #MasterTbl.TourClassId 
                     AND #ColumnsTbl.ColumnText  = #MasterTbl.TourClass
                     AND #ColumnsTbl.ColumnIndex = ' + CAST(@ColumnIndex AS VARCHAR(12))
      EXEC       (@SQLstr)

      SELECT  @ColumnIndex = @ColumnIndex + 1 
   END

DROP TABLE #ColumnsTbl
DROP TABLE #MasterTbl

SELECT     #RowsTbl.* FROM #RowsTbl ORDER BY   #RowsTbl.CityName
End
--DROP TABLE  #RowsTbl

exec uspTmpPivotTableTry ' will fetch u all records even if more Tourclasses added

Results

CityName                               TourClassid HotelId     Deluxe Class                 Fisrt Class   Standard Class    Supperies Class             
-------------------------------------------------- ----------- ---------------------------------------------------------------------------------------
Phnom Penh                             3           1           NULL                         NULL          NULL              City Angkor Hotel
Siem Reap                              1           1           City Angkor Hotel            NULL          NULL              NULL
Sihanouk Vill                          1           3           Sihanouk Ville Hotel         NULL          NULL              NULL

--------------- It doesn't use Pivot method but effective, bcoz we r not fixing the columns

You can Add Few more Tour Class and TourHotel to check.. it works Fine

How to Quiry Pivot 4 Tables

CityName | Deluxe Class | Fisrt Class | Supperies Class | Standard Class |

Phnom Pemh | Phnom Penh Hotel | | | |

Siem Reap | City Angkor Hotel | | | |

SihanoukVille | Sihanouk Ville Hotel | | | |

Follow this question

Related Questions