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

kosaly gravatar image

kosaly
13 1 1 2

(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

Alban Lijo gravatar image

Alban Lijo
36 1

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

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

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

Alban Lijo gravatar image

Alban Lijo
36 1

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:

x742
x82

asked: Jul 07, 2010 at 02:52 AM

Seen: 1165 times

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