question

kosaly avatar image
kosaly asked

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
sqlpivot
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Alban Lijo avatar image
Alban Lijo answered
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..**
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·
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
0 Likes 0 ·
Try commenting out the CREATE UNIQUE INDEX in the Stored Procedure and try .. it should work..
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

There may be a more elegant solution somewhere. I'm still learning my way around the PIVOT statement...
0 Likes 0 ·
Alban Lijo avatar image
Alban Lijo answered
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
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.