x

How to Quiry Pivot 4 Tables

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 Vill
    
    
  2. tblHotel(HotelID, HotelName)

           1  City Angkor Hotel
             2  Phnom Penh Hotel
             3  Sihanouk Ville Hotel
    
    
  3. tblTourClass(TourClassid, TourClass)

           1    Deluxe Class
             2    Fisrt Class
             3    Supperies Class
             4    Standard Class
    
    
  4. 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

more ▼

asked Jul 07, 2010 at 02:52 AM in Default

avatar image

kosaly
13 1 1 4

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first
 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..

more ▼

answered Jul 08, 2010 at 12:33 AM

avatar image

Alban Lijo
36 1 2 2

Dear Sir

many thanks for your help, if I want to display it on asp.net table how can I do can you show me.

thank

Jul 08, 2010 at 12:42 AM kosaly

Please mark it as answer if it is resolved so others can find the answer too .. u can use it in asp.net bcoz it returns a recordset

Jul 08, 2010 at 01:00 AM Alban Lijo

Try commenting out the CREATE UNIQUE INDEX in the Stored Procedure and try .. it should work..

Jul 08, 2010 at 04:14 AM Alban Lijo
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Jul 07, 2010 at 03:38 AM

avatar image

ThomasRushton ♦♦
39.9k 20 49 52

There may be a more elegant solution somewhere. I'm still learning my way around the PIVOT statement...

Jul 07, 2010 at 03:52 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left
 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

more ▼

answered Jul 07, 2010 at 05:20 AM

avatar image

Alban Lijo
36 1 2 2

Dear Sir

many thanks for your help, one more I want you to help 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

Sorry for ask you again. I'm Waiting for your help.

thanks with my best regards Kosal

Jul 07, 2010 at 06:56 PM kosaly

You should add this as a new question to be able to accept an answer to both of your questions. First of all, If you don't it is hard for others that have similar problems and need help to find out what was the solution to your question. Second, the people that help you will probably want to get credit for their help, but the most important is ofcourse the first of my arguments.

Jul 08, 2010 at 12:44 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x994
x103

asked: Jul 07, 2010 at 02:52 AM

Seen: 1311 times

Last Updated: Jul 07, 2010 at 03:07 AM

Copyright 2016 Redgate Software. Privacy Policy