CODEWITHSUNDEEP
sql-serverpivotazure-sql-database
HadoopAddict
HadoopAddict

Reputation: 225

Convert row to 4 columns in sql server

I have a table that looks similar to below :

Maximum there can be only 4 different reason rows for an ID. I want to convert that Reason column into 4 columns and remove the other rows. If an ID doesn't have 4 reasons still split it into 4 columns and make them NULL. If an ID has same reasons repeating just show it in one column and make the other columns NULL.

The reason column need to be split into different columns based on number of distinct reasons

Expected result is as below. Table :

ID  Date    Reason
100 10/27/2017  Insufficient
100 10/27/2017  Excessive
101 10/20/2017  Excessive
101 10/20/2017  Excessive
101 10/20/2017  Insufficient
101 10/20/2017  Derog
105 10/24/2017  Length
106 10/10/2017  Dismiss
107 10/10/2016  Rejected
108 10/10/2016  Dismiss

Expected Result :

ID  Date    Reason1 Reason2 Reason3
100 10/27/2017  Insufficient    Excessive   NULL
101 10/20/2017  Excessive   Insufficient    Derog
105 10/24/2017  Length  NULL    NULL
106 10/10/2017  Dismiss NULL    NULL
107 10/10/2016  Rejected    NULL    NULL
108 10/10/2016  Dismiss NULL    NULL

Upvotes: 0

Views: 65

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

Here is how you would do this if you have a max of 4 columns. Note that if you have fifth column it will NOT appear in this. To handle the number of columns dynamically means we have to use dynamic sql and the complexity jumps pretty quickly.

declare @Something table
(
    ID int
    , MyDate date
    , Reason varchar(20)
)

insert @Something values
(100, '10/27/2017', 'Insufficient')
, (100, '10/27/2017', 'Excessive')
, (101, '10/20/2017', 'Excessive')
, (101, '10/20/2017', 'Excessive')
, (101, '10/20/2017', 'Insufficient')
, (101, '10/20/2017', 'Derog')
, (105, '10/24/2017', 'Length')
, (106, '10/10/2017', 'Dismiss')
, (107, '10/10/2016', 'Rejected')
, (108, '10/10/2016', 'Dismiss')
;

select x.ID
    , Result1 = MAX(case when RowNum = 1 then Reason end)
    , Result2 = MAX(case when RowNum = 2 then Reason end)
    , Result3 = MAX(case when RowNum = 3 then Reason end)
    , Result4 = MAX(case when RowNum = 4 then Reason end)
from
( 
    select *
        , RowNum = ROW_NUMBER() over (partition by ID order by MyDate)
    from @Something
    group by ID, Reason, MyDate
) x
group by x.ID

Upvotes: 3

Related Questions