question

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

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