# How to Quiry Pivot 4 Tables

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:

-----------------------------------------------------------------------------------------------

## 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
13 1 1 2

5 answers: sort oldest
 0 ``````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 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 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 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 ♦ 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 ♦ add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 ``````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 columnsYou can Add Few more Tour Class and TourHotel to check.. it works Fine more ▼ answered Jul 07, 2010 at 05:20 AM Alban Lijo 36 ● 1 Dear Sir many thanks for your help, one more I want you to help as below. tblCity(Cityid, cityName) 1 Phnom Penh 2 Siem Reap 3 Sihanouk 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 tblTourClass(TourClassid, TourClass) 1 Deluxe Class 2 Supperies Class 3 Standard Class 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 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

### New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

### Follow this question

By Email:

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

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

Topics:

x737
x82

asked: Jul 07, 2010 at 02:52 AM

Seen: 1137 times

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