Pivot Table issue: no identifier in original table or enumeration

Question

I'm having an issue with using pivot or dynamic tables.

I found this example to be very helpful in understanding how I can accomplish this task; however, I am missing the inside identifier column.

Dynamic Pivot Columns in SQL Server

There is an SQL fiddle provided here in the post above: http://www.sqlfiddle.com/#!3/7fad2/6

You can see that in the second table propertyObjects, there is a count 1, 2, 3, 4 for each objectID. I do not have that propertyID count. This is all I have

case  category
1      xx
1      xyx
1      abc
2      ghj
2      asdf
3      dfgh

As you can see I have a number of different categories for each case, but no category identifier field.

This is what I need:

case  cat1  cat2  cat3
1     xx    xyx   abc
2     ghj   asdf
3     dfgh

So I am thinking I might need to add a column to the source table and somehow enumerate the categories per case. This would make it possible for me to use the pivot in the provided example. Thoughts?

I tried to use row_number to accomplish this, but it does not stop at each case number, it just continues on counting the entire table.

Answer 1

Since you have multiple values for each case, then you will need to use row_number() to get the separate columns for each category.

Before you write the dynamic SQL version I would first write a hard-coded version. The code will be similar to:

SELECT [case], cat1, cat2, cat3
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 (cat1, cat2, cat3)
)p;

See SQL Fiddle with Demo.

Now you have the logic down, then you can convert it to dynamic SQL:

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;

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

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