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

pivotsql server

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

Best Answer

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.

Related Question