SQL Server Pivot – Join Two Tables Combining Columns

pivotsql serversql server 2014

I have two tables in this format:

Job_Skill

ID_Skill  Min_Job  Idea_Job   Max_Job
=====================================
1        0           0       1
2        0           1       1
7        1           1       1
8        1           1       1

Job_Education

Degree_Job  Field_Job  Min_Job   Idea_Job   Max_Job
===================================================
7        37         0         0         1
7        106        0         1         1
13       37         1         1         1
13       106        1         1         1

Desired Result

Criteria   1  2  7  8 [ 7  37 ] [ 7 106 ]  [13 37]   [13 106]
=============================================================
Min        0  0  1  1    1         1         0          0   
Ideal      0  1  1  1    0         1         1          1
Max        1  1  1  1    1         1         1          1

How can I achieve this using a pivot, or any other method?

I am using SQL Server 2014. The columns are not fixed; they are dynamic. A static version is also acceptable, as I just want to understand how I should approach this.

Best Answer

You can UNPIVOT and then PIVOT.

Half of it is below (untested):

WITH P1_Src As
(
SELECT ID_Skill, Val, Col
FROM Job_Skill
UNPIVOT (Val FOR Col IN (Min_Job, Idea_Job, Max_Job)) AS U
), P1 AS
(
SELECT Col, [1], [2], [7], [8]
FROM P1_Src
PIVOT (MAX(Val) FOR ID_Skill IN ([1], [2], [7], [8])) AS P
)
SELECT *
FROM P1

You would need to apply a case expression to get the desired Criteria string, then extend the example to do the same for the other table and join the two results on Criteria.

To make this dynamic, you would need to look at the places in the query that currently have hard-coded values, extract the distinct values from the table instead, and use these instead of the hardcoded values.

For an example of the basic technique, see Dynamic pivot: sum of sales per month or search for "dynamic pivot".

Related Solutions

SQL Server 2008 – Change Display of Rows into Single Row

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

Sql-server – Dynamic Creation of Table using Stored Procedure Without a Use of OpenRowSet in SQL Server

If you cannot use OPENROWSET, you could create a loopback linked server and use OPENQUERY or EXECUTE AT.

DECLARE @srv nvarchar(4000);
SET @srv = @@SERVERNAME; -- gather this server name

-- Create the linked server
EXEC master.dbo.sp_addlinkedserver
@server     = N'LOOPBACK',
@srvproduct = N'SQLServ', -- it’s not a typo: it can’t be “SQLServer”
@provider   = N'SQLNCLI', -- change to SQLOLEDB for SQLServer 2000
@datasrc    = @srv;


-- Set the authentication to "current security context"
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname  = N'LOOPBACK',
@useself     = N'True',
@locallogin  = NULL,
@rmtuser     = NULL,
@rmtpassword = NULL;

I blogged about it some years ago.

Another option is using some kind of CLR trickery to interpret the result set definition and create a target table based on the type and length of the columns.

If you're not allowed to use OPENROWSET, I highly doubt you will be allowed to use CLR, though. In this case, a third option could be using sp_describe_first_result_set and code your table creation script in T-SQL. It requires SQL Server 2012 or newer.