Dynamic pivot with similar column names

Question

I'm looking for a way to pivot a varying amount of rows to columns in sql server 2008 R2. I created the data column RANK in the query because, ultimately I want the pivoted column names to be labeled the value in the RANK column. Then, if somehow I can STUFF the other 3 field values together into one field I would be able to iterate over the row in my backend lang and split the field results appropriately.

Here is the current data set:

And I would like the end result of the pivot to produce a result like this:

I haven't found anything about being able to pivot in this "dynamic" way. Any help would be much appreciated.

Answer 1

As I mentioned above, you need to distinguish each of your Rank values. You've said that this is a calculated value that you can add a number to the end of each one. Once you've added that number, then you still need to pivot it.

The easiest way to first see this would be to write a hard-coded version of the query first.

Sample Data:

create table yourdata
(
    id int,
    code varchar(50),
    created datetime,
    [rank] varchar(50)
);

insert into yourdata
select 285856, 'J7609', '2015-01-19', 'Principle' union all
select 285856, 'J7613', '2015-01-19', 'Other' union all
select 285856, 'J0456', '2015-01-19', 'Other' union all
select 285856, 'J0694', '2015-01-19', 'Other' union all
select 285856, 'J1885', '2015-01-19', 'Other' union all
select 285856, 'J2060', '2015-01-19', 'Other' union all
select 285856, 'J2930', '2015-01-19', 'Other';

Static Query:

select Principle_1, Other_1, 
    Other_2, Other_3, Other_4,
    Other_5, Other_6
from
(
    -- using row_number to get unique id for each rank
    select 
        data = cast(id as varchar(10)) +' | '+ code +' | '+ convert(varchar(10), created, 112),     
        [rank] = [rank] + '_' +cast(row_number() over(partition by id, [rank] 
                                                        order by id) as varchar(10))
    from yourdata
) d
pivot
(
    max(data)
    for [rank] in (Principle_1, Other_1, Other_2, Other_3, Other_4,
                    Other_5, Other_6)
) p;

Now to do this dynamic, you will create a sql string with the column names and then execute that string:

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

select @cols = STUFF((SELECT  ',' + QUOTENAME([rank] + '_' +cast(rn as varchar(10))) 
                    from
                    (
                        select [rank],
                            rn = row_number() over(partition by id, [rank] 
                                                    order by id) 
                        from  yourdata
                    ) d
                    group by [rank], rn
                    order by rn, [rank] desc
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + '
            from 
             (
                select 
                    data = cast(id as varchar(10)) +'' | ''+ code +'' | ''+ convert(varchar(10), created, 112),     
                    [rank] = [rank] + ''_'' +cast(row_number() over(partition by id, [rank] 
                                                                    order by id) as varchar(10))
                from yourdata
            ) x
            pivot 
            (
                max(data)
                for [rank] in (' + @cols + ')
            ) p '

exec sp_executesql @query;

This gets you a result:

+---------------------------+---------------------------+---------------------------+---------------------------+---------------------------+---------------------------+---------------------------+
|        Principle_1        |          Other_1          |          Other_2          |          Other_3          |          Other_4          |          Other_5          |          Other_6          |
+---------------------------+---------------------------+---------------------------+---------------------------+---------------------------+---------------------------+---------------------------+
| 285856 | J7609 | 20150119 | 285856 | J7613 | 20150119 | 285856 | J0456 | 20150119 | 285856 | J0694 | 20150119 | 285856 | J1885 | 20150119 | 285856 | J2060 | 20150119 | 285856 | J2930 | 20150119 |
+---------------------------+---------------------------+---------------------------+---------------------------+---------------------------+---------------------------+---------------------------+