Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 -

share|improve this question
    
Have you tried writing this query as a static version to get the logic correct? Without seeing your data and the final desired result, I'd only be guessing but I don't see why you are unpivoting the columns Beds and HotelSeqId. Can you edit your post with some sample data and then the final result, or even better create a sql fiddle? –  bluefeet Jul 1 at 21:26
    
It can't be a static version as nnumber of rooms in each floor are different, hence the output criteria can not be met. –  user41979 Jul 2 at 7:33

1 Answer 1

up vote 4 down vote accepted

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 and Beds - 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 using Beds and HotelSeqID. You don't want to unpivot HotelSeqID 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:

select h.HotelName, 
  r.FloorNo,
  r.RoomNo,
  r.Beds,
  seq = row_number() over(partition by h.HotelName, r.FloorNo
                          order by r.RoomNo) 
from dbo.Hotels h
inner join dbo.HotelRooms r
  on h.seqid = r.hotelseqid

See SQL Fiddle with Demo. Your data will look like this with a new column that contains the sequence number based on the HotelName and FloorNo:

|                 HOTELNAME | FLOORNO | ROOMNO | BEDS | SEQ |
|---------------------------|---------|--------|------|-----|
| Hotel 1                   |       1 |    101 |    1 |   1 |
| Hotel 1                   |       1 |    102 |    2 |   2 |
| Hotel 1                   |       1 |    103 |    1 |   3 |
| Hotel 1                   |       1 |    104 |    2 |   4 |
| Hotel 1                   |       2 |    201 |    1 |   1 |
| Hotel 1                   |       2 |    202 |    2 |   2 |

Now, you can unpivot the RoomNo and Beds columns into multiple rows. Since you are using SQL Server 2008 you can use CROSS APPLY to get the result. The query will be:

select hr.HotelName, 
  hr.FloorNo, 
  col = c.col + '_' + cast(seq as varchar(2)),
  c.val
from
(
  select h.HotelName, 
    r.FloorNo,
    r.RoomNo,
    r.Beds,
    seq = row_number() over(partition by h.HotelName, r.FloorNo
                            order by r.RoomNo) 
  from dbo.Hotels h
  inner join dbo.HotelRooms r
    on h.seqid = r.hotelseqid
) hr
cross apply
(
  select 'RoomNo', RoomNo union all
  select 'Beds', Beds
) c (col, val);

See SQL Fiddle with Demo. Your data has now been transformed into multiple columns:

|                 HOTELNAME | FLOORNO |      COL | VAL |
|---------------------------|---------|----------|-----|
| Hotel 1                   |       1 | RoomNo_1 | 101 |
| Hotel 1                   |       1 |   Beds_1 |   1 |
| Hotel 1                   |       1 | RoomNo_2 | 102 |
| Hotel 1                   |       1 |   Beds_2 |   2 |
| Hotel 1                   |       1 | RoomNo_3 | 103 |
| Hotel 1                   |       1 |   Beds_3 |   1 |
| Hotel 1                   |       1 | RoomNo_4 | 104 |
| Hotel 1                   |       1 |   Beds_4 |   2 |
| Hotel 1                   |       2 | RoomNo_1 | 201 |

Finally, you can pivot to get the final result.

select HotelName, FloorNo,
  RoomNo_1, Beds_1, RoomNo_2, Beds_2,
  RoomNo_3, Beds_3, RoomNo_4, Beds_4
from
(
  select hr.HotelName, 
    hr.FloorNo, 
    col = c.col + '_' + cast(seq as varchar(2)),
    c.val
  from
  (
    select h.HotelName, 
      r.FloorNo,
      r.RoomNo,
      r.Beds,
      seq = row_number() over(partition by h.HotelName, r.FloorNo
                              order by r.RoomNo) 
    from dbo.Hotels h
    inner join dbo.HotelRooms r
      on h.seqid = r.hotelseqid
  ) hr
  cross apply
  (
    select 'RoomNo', RoomNo union all
    select 'Beds', Beds
  ) c (col, val)
) d
pivot
(
  max(val)
  for col in (RoomNo_1, Beds_1, RoomNo_2, Beds_2,
              RoomNo_3, Beds_3, RoomNo_4, Beds_4)
) piv
order by HotelName, FloorNo;

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.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(col + '_' + cast(seq as varchar(2))) 
                    from
                    (
                      select seq = row_number() over(partition by h.HotelName, r.FloorNo
                                                      order by r.RoomNo) 
                      from dbo.Hotels h
                      inner join dbo.HotelRooms r
                        on h.seqid = r.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 = N'SELECT HotelName, FloorNo,' + @cols + N' 
            from 
            (
              select hr.HotelName, 
                hr.FloorNo, 
                col = c.col + ''_'' + cast(seq as varchar(2)),
                c.val
              from
              (
                select h.HotelName, 
                  r.FloorNo,
                  r.RoomNo,
                  r.Beds,
                  seq = row_number() over(partition by h.HotelName, r.FloorNo
                                          order by r.RoomNo) 
                from dbo.Hotels h
                inner join dbo.HotelRooms r
                  on h.seqid = r.hotelseqid
              ) hr
              cross apply
              (
                select ''RoomNo'', RoomNo union all
                select ''Beds'', Beds
              ) c (col, val)
            ) x
            pivot 
            (
                max(val)
                for col in (' + @cols + N')
            ) p 
            order by HotelName, FloorNo'

exec sp_executesql @query

See SQL Fiddle with Demo. This query will give you the result:

|                 HOTELNAME | FLOORNO  |  ROOMNO_1 | BEDS_1 | ROOMNO_2 | BEDS_2 | ROOMNO_3 | BEDS_3 | ROOMNO_4 | BEDS_4 | ROOMNO_5 | BEDS_5 | ROOMNO_6 | BEDS_6 |
|---------------------------|----------|-----------|--------|----------|--------|----------|--------|----------|--------|----------|--------|----------|--------|
| Hotel 1                   |        1 |       101 |     1  |      102 |      2 |      103 |      1 |      104 |      2 |   (null) | (null) |   (null) | (null) |
| Hotel 1                   |        2 |       201 |     1  |      202 |      2 |      203 |      1 |      204 |      2 |      205 |      1 |      206 |      2 |
| Hotel 2                   |        1 |       101 |     4  |      102 |      6 |   (null) | (null) |   (null) | (null) |   (null) | (null) |   (null) | (null) |
| Hotel 2                   |        2 |       201 |     2  |      202 |      7 |   (null) | (null) |   (null) | (null) |   (null) | (null) |   (null) | (null) |
share|improve this answer
    
The above dynamic query is giving me an error in displaying in the RoomNo_1 column data which has to be 101 in first row instead of 1, The dynamically generated column 2 gives correct data. Please check for a solution to correct column RoomNo_1 data. @bluefeet –  user41979 Jul 3 at 8:58
    
@user41979 Should be fixed there was a comma missing in the final select list. I'll update the SQL fiddle demo when the site is back up. –  bluefeet Jul 3 at 12:55
    
I had figured it out - Thanks for hinting me - @bluefeet –  user41979 Jul 3 at 19:01
2  
@user41979 That's an answer worth a book chapter, not a hint. If it worked and you're happy with it, you might wanna use the arrow and the mark sign near it. You most likely won't get a better answer on the SE network regarding pivots. –  Marian Jul 3 at 19:15
2  
@user41979 Please don't edit this answer to ask another question, also don't edit your existing question. If you have additional questions, then ask a new question with the details needed for an answer. –  bluefeet Jul 5 at 12:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.