Pivot Table Missing Column

Question

I am trying to use a pivot to get information in a diff format.

Here is my table:

    CREATE TABLE yourtable
([case] int, [category] varchar(4))
    ;

    INSERT INTO yourtable
([case], [category])
    VALUES
(1, 'xx'),
(1, 'xyx'),
(1, 'abc'),
(2, 'ghj'),
(2, 'asdf'),
(3, 'dfgh')
    ; 

Here is my pivot command courtesy of bluefeet:

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

    select @cols = STUFF((SELECT distinct ',' + QUOTENAME('cat'+cast(seq as    
    varchar(10))) 
                from
                (
                  select row_number() over(partition by [case] 
                                           order by category) seq
                  from yourtable
                ) d
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

    set @query = 'SELECT [case],' + @cols + ' 
        from 
        (
          SELECT [case], category,
            ''cat''+
              cast(row_number() over(partition by [case] 
                                     order by category) as varchar(10)) seq
          FROM yourTable
        ) x
        pivot 
        (
            max(category)
            for seq in (' + @cols + ')
        ) p '

    execute sp_executesql @query;

The output is good, it is in the format I need.

CASE    CAT1    CAT2    CAT3
1        abc    xx    xyx
2        asdf   ghj   (null)
3        dfgh   (null)   (null)

However, I also need to add additional columns to the table. The modified table would be as follows, but I'm not sure how to add this to the QUOTENAME.

    CREATE TABLE yourtable
    ([case] int, [category] varchar(4), [status] varchar(4))
    ;

    INSERT INTO yourtable
    ([case], [category], [status])
    VALUES
    (1, 'xx', '00'),
    (1, 'xyx', '01'),
    (1, 'abc', '00'),
    (2, 'ghj', '01'),
    (2, 'asdf', '00'),
    (3, 'dfgh', '01')
     ; 

How can this be done? Should I add an additional QUOTENAME command? Results should be:

    CASE    CAT1    status1    CAT2      status2      CAT3      status3
    1       abc     00         xx          00         xyx        01
    2       asdf    00         ghj         01       (null)    (null)
    3       dfgh    01        (null)     (null)     (null)    (null)

Answer 1

Since you now have two columns that you want to PIVOT, you can first unpivot the category and status columns into a single column with multiple rows.

There are a few different ways you can unpivot the data, you can use UNPIVOT or CROSS APPLY. The basic syntax will be:

select [case],
  col+cast(seq as varchar(10)) seq,
  value
from
(
  SELECT [case], status, category,
    row_number() over(partition by [case] 
                      order by status) seq
  FROM yourTable
) d
cross apply
(
  select 'cat', category union all
  select 'status', status
) c (col, value)

See SQL Fiddle with Demo This will convert your multiple columns of data into something that looks like this:

| CASE |     SEQ | VALUE |
|------|---------|-------|
|    1 |    cat1 |    xx |
|    1 | status1 |    00 |
|    1 |    cat2 |   abc |
|    1 | status2 |    00 |
|    1 |    cat3 |   xyx |
|    1 | status3 |    01 |
|    2 |    cat1 |  asdf |
|    2 | status1 |    00 |

Once the data is in this format, then you can apply the PIVOT function to it.

SELECT [case], cat1, status1, cat2, status2, cat3, status3
FROM
(
  select [case],
    col+cast(seq as varchar(10)) seq,
    value
  from
  (
    SELECT [case], status, category,
      row_number() over(partition by [case] 
                        order by status) seq
    FROM yourTable
  ) d
  cross apply
  (
    select 'cat', category union all
    select 'status', status
  ) c (col, value)
) x
PIVOT
(
   max(value)
   for seq in (cat1, status1, cat2, status2, cat3, status3)
)p;

See SQL Fiddle with Demo

Then you can convert it to dynamic SQL:

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

select @cols = STUFF((SELECT  ',' + QUOTENAME(col+cast(seq as varchar(10))) 
                    from
                    (
                      select row_number() over(partition by [case] 
                                               order by category) seq
                      from yourtable
                    ) d
                    cross apply
                    (
                      select 'cat', 1 union all
                      select 'status', 2 
                    ) c (col, so)
                    group by seq, col, so
                    order by seq, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT [case],' + @cols + ' 
            from 
            (
              select [case],
                col+cast(seq as varchar(10)) seq,
                value
              from
              (
                SELECT [case], status, category,
                  row_number() over(partition by [case] 
                                    order by status) seq
                FROM yourTable
              ) d
              cross apply
              (
                select ''cat'', category union all
                select ''status'', status
              ) c (col, value)
            ) x
            pivot 
            (
                max(value)
                for seq in (' + @cols + ')
            ) p '

execute sp_executesql @query;

See SQL Fiddle with Demo The final result will be:

| CASE | CAT1 | STATUS1 |   CAT2 | STATUS2 |   CAT3 | STATUS3 |
|------|------|---------|--------|---------|--------|---------|
|    1 |   xx |      00 |    abc |      00 |    xyx |      01 |
|    2 | asdf |      00 |    ghj |      01 | (null) |  (null) |
|    3 | dfgh |      01 | (null) |  (null) | (null) |  (null) |