CODEWITHSUNDEEP
sqlsql-serverpivot
user1744002
user1744002

Reputation: 31

Converting multiple rows to multiple columns

This is with SQL server 

ID       ID1      Days     Rank
96972    1        2976     1
96972    174      2976     2
96972    173      2966     3
96972    175      2966     4
96972    176      2963     5
96972    177      2961     6
96972    178      2960     7
96972    179      2952     8
96972    180      2945     9
96972    181      135      10

I did Rank column in the result set as below 

row_number() OVER(Partition by id ORDER BY somedate)  AS TimeLineOrders

I would like show these results in single row as

Timeline1     Timeline2     TimeLine3     Timeline4    Timeline 10
96972         ID1+Days      ID1+Days      ID1+Days     ID1+Days     ID1+Days

Can anyone help me please?

Upvotes: 3

Views: 4826

Answers (1)

Taryn
Taryn

Reputation: 247880

It is difficult to know exactly what you are looking for due to the lack of table structure and final result. It seems like you need to PIVOT the data. You can PIVOT either with a static version of dynamic version.

Create table with data sample:

create table tableA
(
    id int,
    id1 int,
    days int
);

insert into tableA values
(96972,   1,   2976),
(96972,   174, 2976),
(96972,   173, 2966),
(96972,   175, 2966),
(96972,   176, 2963),
(96972,   177, 2961),
(96972,   178, 2960),
(96972,   179, 2952),
(96972,   180, 2945),
(96972,   181, 135);

Static Version, has the values hard-coded to pivot:

select *
from 
(
    select id, 
        'Timeline_' + cast(ROW_NUMBER() over(PARTITION by id order by days desc) as varchar(10)) col,
        id1 + days value
    from tableA
) x
pivot
(
    max(value)
    for col in ([Timeline_1], [Timeline_2], [Timeline_3], [Timeline_4], 
                [Timeline_5], [Timeline_6], [Timeline_7], [Timeline_8], 
                [Timeline_9], [Timeline_10])
) p

Dynamic Version, columns are generated at run-time:

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

select @cols = STUFF((SELECT ',' + QUOTENAME( 'Timeline_' + cast(x.rn as varchar(10))) 
                    from tableA
                    cross apply 
                    (
                        select ROW_NUMBER() over(PARTITION by id order by days desc) rn
                        from tableA
                    ) x
                    group by x.rn
                    order by x.rn
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id, ' + @cols + ' from 
             (
                select id, 
                    ''Timeline_'' + cast(ROW_NUMBER() over(PARTITION by id order by days desc) as varchar(10)) col,
                    id1 + days value
                from tableA
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute(@query)

Upvotes: 3

Related Questions