I have 2 tables -
USE [Test1July]
GO
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
USE [Test1July]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[hpt_HotelRooms](
[SEQ_ID] [int] IDENTITY(1,1) NOT NULL,
[HotelSEQ_ID] [int] NULL,
[FloorNo] [int] NULL,
[RoomNo] [varchar](10) NULL,
[Beds] [int] NULL,
[Description] [varchar](100) NULL,
[SharingType] [nvarchar](1) NULL,
[PaxType] [varchar](1) NULL,
[Active] [bit] NULL,
[DisplayOrder] [smallint] NULL,
CONSTRAINT [PK_hpt_Rooms] PRIMARY KEY CLUSTERED
(
[SEQ_ID] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[hpt_HotelRooms] WITH CHECK ADD CONSTRAINT [FK_hpt_HotelRooms_Hotels] FOREIGN KEY([HotelSEQ_ID])
REFERENCES [dbo].[Hotels] ([SeqID])
GO
ALTER TABLE [dbo].[hpt_HotelRooms] CHECK CONSTRAINT [FK_hpt_HotelRooms_Hotels]
GO
I have been successful in generating the desired results by pivoting - Here's the query that I have developed.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@HotelSeqID AS INT
SET @HotelSeqID = 1
select @cols = STUFF((SELECT ',' + QUOTENAME(col + '_' + cast(seq as varchar(2)))
from
(
select seq = row_number() over(partition by h.SeqID, r.FloorNo
order by r.RoomNo)
from dbo.Hotels h
inner join dbo.hpt_HotelRooms r
on h.seqid = r.HotelSEQ_ID
WHERE h.SeqID = @HotelSeqID
) d
cross apply
(
select 'RoomNo', 1 union all
select 'Beds', 2
) c (col, so)
group by col, so, seq
order by seq, so
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT SeqID, FloorNo, ' + @cols + '
from
(
select hr.SeqID,
hr.FloorNo,
col = c.col + ''_'' + cast(seq as varchar(2)),
c.val
from
(
select h.SeqID,
r.FloorNo,
r.RoomNo,
r.Beds,
seq = row_number() over(partition by h.SeqID, r.FloorNo
order by r.RoomNo)
from dbo.Hotels h
inner join dbo.hpt_HotelRooms r
on h.seqid = r.HotelSEQ_ID
WHERE h.seqid = ' + CONVERT ( NVARCHAR , @HotelSeqID, 2 ) + '
) hr
cross apply
(
select ''RoomNo'', RoomNo union all
select ''Beds'', Beds
) c (col, val)
) x
pivot
(
max(val)
for col in (' + @cols + ')
) p
order by SeqID, FloorNo'
exec sp_executesql @query
It works fine but when I try to add other columns in it - It gives me conversion errors - Why is that so and what could be best possible workaround of it.
Here is my query that I am trying to fix
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@HotelSeqID AS INT
SET @HotelSeqID = 1
select @cols = STUFF((SELECT ',' + QUOTENAME(col + '_' + cast(seq as varchar(2)))
from
(
select seq = row_number() over(partition by h.SeqID, r.FloorNo
order by r.RoomNo)
from dbo.Hotels h
inner join dbo.hpt_HotelRooms r
on h.seqid = r.HotelSEQ_ID
WHERE h.SeqID = @HotelSeqID
) d
cross apply
(
select 'RoomNo', 1 union all
select 'Beds', 2 union all
select '[Description]',3 union all
select 'SharingType',4 union all
select 'PaxType',5
) c (col, so)
group by col, so, seq
order by seq, so
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT SeqID, FloorNo, ' + @cols + '
from
(
select hr.SeqID,
hr.FloorNo,
col = c.col + ''_'' + cast(seq as varchar(2)),
c.val
from
(
select h.SeqID,
r.FloorNo,
r.RoomNo,
r.Beds,
r.[Description],
r.SharingType,
r.PaxType,
seq = row_number() over(partition by h.SeqID, r.FloorNo
order by r.RoomNo)
from dbo.Hotels h
inner join dbo.hpt_HotelRooms r
on h.seqid = r.HotelSEQ_ID
WHERE h.seqid = ' + CONVERT ( NVARCHAR , @HotelSeqID, 2 ) + '
) hr
cross apply
(
select ''RoomNo'', RoomNo union all
select ''Beds'', Beds union all
select ''[Description]'', [Description] union all
select ''SharingType'', SharingType union all
select ''PaxType'',5 PaxType
) c (col, val)
) x
pivot
(
max(val)
for col in (' + @cols + ')
) p
order by SeqID, FloorNo'
exec sp_executesql @query
