I have created a db that contains the following 2 small tables
Here is the db script
USE [Test1July]
GO
/****** Object: Table [dbo].[Hotels] Script Date: 07/01/2014 22:33:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Hotels](
[SeqID] [int] IDENTITY(1,1) NOT NULL,
[HotelName] [nchar](25) NULL,
CONSTRAINT [PK_Hotels] PRIMARY KEY CLUSTERED
(
[SeqID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[HotelRooms] Script Date: 07/01/2014 22:33:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HotelRooms](
[SeqID] [int] IDENTITY(1,1) NOT NULL,
[HotelSeqID] [int] NOT NULL,
[FloorNo] [int] NOT NULL,
[RoomNo] [int] NOT NULL,
[Beds] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object: StoredProcedure [dbo].[GetFloorStructure] Script Date: 07/01/2014 22:33:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetFloorStructure](
@HotelSeqID INT
)
AS
BEGIN
SELECT HotelRooms.FloorNo, HotelRooms.RoomNo, HotelRooms.Beds
FROM HotelRooms INNER JOIN
Hotels ON HotelRooms.HotelSeqID = Hotels.SeqID
WHERE (HotelRooms.HotelSeqID = @HotelSeqID)
END
GO
/****** Object: ForeignKey [FK_HotelRooms_Hotels] Script Date: 07/01/2014 22:33:33 ******/
ALTER TABLE [dbo].[HotelRooms] WITH CHECK ADD CONSTRAINT [FK_HotelRooms_Hotels] FOREIGN KEY([HotelSeqID])
REFERENCES [dbo].[Hotels] ([SeqID])
GO
ALTER TABLE [dbo].[HotelRooms] CHECK CONSTRAINT [FK_HotelRooms_Hotels]
GO
I am looking for a solution that displays the floor structure of a hotels floor showing room data like roomNo and beds not vertically – i.e I should be able to view data in a single row for floor 1 of a particular data. I have been trying it out with dynamic PIVOT but my knowledge is very limited to PIVOT.
I have managed to create a dynamic PIVOT query but not getting the expected result. Kindly advice what can be done to achieve the multiple row results in one single row with in dynamically generated colums – Here is my query
DECLARE @colsPivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @colsPivot = STUFF((SELECT ',' + QUOTENAME(c.col + '_'+cast(rn as varchar(10)))
from
(
select row_number() over(partition by FloorNo, RoomNo
order by Beds) rn
from HotelRooms
) t
cross apply
(
select 'Beds' col, 1 so union all
select 'HotelSeqID', 2
) c
group by col, rn, so
order by rn, so
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select FloorNo, RoomNo, '+@colsPivot+'
from
(
select FloorNo, RoomNo,
col+''_''+cast(rn as varchar(10)) col,
val
from
(
select FloorNo, RoomNo, Beds, HotelSeqID
, row_number() over(partition by FloorNo, RoomNo
order by Beds) rn
from HotelRooms
) d
unpivot
(
val
for col in (Beds, HotelSeqID)
) un
) s
pivot
(
max(val)
for col in ('+ @colspivot +')
) p'
exec(@query);
Here is the output I get from a simple select query
FloorNo RoomNo Beds
1 101 1
1 102 2
1 103 1
1 104 2
2 201 1
2 202 2
2 203 1
2 204 2
2 205 1
2 206 2
But I want the output in this format
FloorNo RoomNo Beds RoomNo Beds RoomNo Beds
1 101 1 102 2 103 1
Here's the STATIC version of the query that gives me the desired result
select HotelSeqID, FloorNo,
max(case when rn = 1 then RoomNo end) RoomNo,
max(case when rn = 1 then Beds end) Beds,
max(case when rn = 2 then RoomNo end) RoomNo,
max(case when rn = 2 then Beds end) Beds,
max(case when rn = 3 then RoomNo end) RoomNo,
max(case when rn = 3 then Beds end) Beds,
max(case when rn = 4 then RoomNo end) RoomNo,
max(case when rn = 4 then Beds end) Beds,
max(case when rn = 5 then RoomNo end) RoomNo,
max(case when rn = 5 then Beds end) Beds
from
(
select HotelSeqID, FloorNo, RoomNo, Beds,
row_number() over(partition by HotelSeqID ORDER BY FloorNo) rn
from HotelRooms
WHERE (HotelRooms.HotelSeqID = @HotelSeqID)
) src
group by HotelSeqID, FloorNo;
All I need is a dynamic version of this query –
Best Answer
Since you want to use
PIVOT
to get the result and you need to to this dynamically, I would always suggest writing a static PIVOT query first, this allows you to get the syntax correct before trying to convert it to dynamic SQL.The desired output shows that you want to PIVOT on two columns,
RoomNo
andBeds
- as a result you'll need to unpivot these columns first, then apply the pivot.Your current query is on the right track, you do need to use
row_number()
so you can get the number of rooms/beds on each floor - but your unpivot is usingBeds
andHotelSeqID
. You don't want to unpivotHotelSeqID
because that doesn't have a value that you eventually want as a new column.I'd start a static version the following way - first the subquery to get the data from your tables vertically with the
row_number()
included:See SQL Fiddle with Demo. Your data will look like this with a new column that contains the sequence number based on the
HotelName
andFloorNo
:Now, you can unpivot the
RoomNo
andBeds
columns into multiple rows. Since you are using SQL Server 2008 you can useCROSS APPLY
to get the result. The query will be:See SQL Fiddle with Demo. Your data has now been transformed into multiple columns:
Finally, you can pivot to get the final result.
See SQL Fiddle with Demo. Once you've tested a static version to make sure it gets your the desired result, you can easily convert this into dynamic SQL.
See SQL Fiddle with Demo. This query will give you the result: