change display of rows into single row in SQL server 2008 using dynamic PIVOTS

Question

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

Answer 1

The problem is with your CROSS APPLY or unpivot. When you unpivot data it will then be placed in the same column so the datatype must be the same.

Your code is using to unpivot is:

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)

This process takes these separate columns and places them into a single column. The problem is that you have different datatypes for each of these columns. RoomNo is a varchar, Beds in an int, Description is a varchar, SharingType in a nvarchar and PaxType is a varchar.

You can fix this but converting the datatype to be the same on all columns. You can perform this conversion in your subquery or in the CROSS APPLY

Subquery:

from
(
 select h.SeqID, 
 r.FloorNo,
 r.RoomNo,
 Beds = cast(r.Beds as varchar(10)), -- cast all columns here
 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

Or inside your CROSS APPLY:

cross apply
(
 select ''RoomNo'', RoomNo union all
 select ''Beds'', cast(Beds as varchar(10)) union all -- cast all columns here
 select ''[Description]'', [Description] union all
 select ''SharingType'', SharingType union all
 select ''PaxType'',5 PaxType
) c (col, val)

Once the data is in the same datatype, then you should no longer get the error.