T-sql – Dynamic SQL in a pivot table

dynamic-sqlpivott-sql

I was watching a video on pivot tables and got to thinking, why can't we just push in a sub query into the IN clause, instead of declaring the expected columns statically? Well, turns out you cant haha, but I wasn't satisfied with that so after some research found that Dynamic SQL will allow you to do something similar. Now, I'm not expert on Dynamic SQL by far, but for the most part I understand what is going on. I am, however, having an issue with a specific query. Here is the query:

DECLARE @SQLString AS NVARCHAR(MAX)
        , @custID AS NVARCHAR(MAX) ;
SELECT @Custid = ISNULL(@custid + ',','') + QUOTENAME(custid)
FROM (SELECT Distinct custid FROM Sales.Orders) AS custid

SET @SQLString = N'
With ptable AS
(
SELECT empid, custid , orderid
FROM sales.Orders
)
SELECT empid, ' + @custID + N'
FROM ptable
PIVOT (COUNT(orderid) For custid IN(' + @custID + N')) AS pivtable
ORDER BY empid'
EXEC @SQLString;

The idea here is to have all of the distinct customer ID's found in the order's table as a separate column to track all employee-customer orders. (This is all for practice and in the name of figuring out how SQL works). This table happens to have 91 unique customers. Here's the problem, it works for the first @CustID in the SELECT statement, however for the second @custid in the IN clause, it only returns the first 16 and a missing ] after the 16. Here is the outputted error message:

Msg 203, Level 16, State 2, Line 16 The name ' With ptable AS (
SELECT empid, custid , orderid FROM sales.Orders ) SELECT empid,
[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52],[53],[54],[55],[56],[58],[59],[60],[61],[62],[63],[64],[65],[66],[67],[68],[69],[70],[71],[72],[73],[74],[75],[76],[77],[78],[79],[80],[81],[82],[83],[84],[85],[86],[87],[88],[89],[90],[91]
FROM ptable PIVOT (COUNT(orderid) For custid
IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16'
is not a valid identifier.

I am struggling to figure out why it worked for the first select clause then failed on the second one. I know this is a ridiculous example, but I guess my curiosity got the better of me and I just want to know. Any help is appreciated!

Best Answer

You are saying:

EXEC @SQLString;

This is like saying:

EXEC N'SELECT 1;';

Which is probably something that looks like it will work on first glance, but go ahead and try it, it fails. You want:

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'SELECT ...;';
EXEC sys.sp_executesql @sql;

I wrote a pretty detailed article on dynamic pivot here:

https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

But as for dynamic SQL, please see:

Related Solutions

Sql-server – Script to query multiple instances

There's lots of options.

PowerShell
SSMS's Multi Server Query Functionality
Linked Servers
Batch File with a loop and sqlcmd variables

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

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) |

Best Answer

Related Solutions

Sql-server – Script to query multiple instances

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

Related Question