Denormalizing result set

Question

I'm trying to denormalize a result set so that I have one record per ID. This is a list of patients with multiple comorbidities. The data currently looks like this:

ID  Disease
1   Asthma  
1   Cancer
1   Anemia
2   Asthma  
2   HBP

And I need it to look like this:

ID  Disease1    Disease2    Disease3
1   Asthma      Cancer      Anemia
2   Asthma      HBP         <NULL or Blank>

I researched Pivot, but all of the examples I saw used aggregate functions which wouldn't apply. I have added the row_number function and tried self joins like the following:

case when rownum = 1 then Disease else NULL end Disease1,
case when rownum = 2 then Disease else NULL end Disease2,
case when rownum = 3 then Disease else NULL end Disease3

However, this produces the following:

ID  Disease1    Disease2    Disease3
1   Asthma      NULL        NULL
1   NULL        Cancer      NULL
1   NULL        NULL        Anemia
2   Asthma      NULL        NULL
2   NULL        HBP         NULL

Any suggestions would be greatly appreciated. I would really like to find a way to accomplish this without having a monstrous block of code (which is what I ended up with when trying to do it). Thanks!

Answer 1

You can use MAX to compact the rows:

select 
    id, 
    max(case when rownum = 1 then Disease end) Disease1,
    max(case when rownum = 2 then Disease end) Disease2,
    max(case when rownum = 3 then Disease end) Disease3
from (
    select 
    id, 
    disease, 
    rownum =  ROW_NUMBER() OVER (partition by id order by id) 
    from your_table 
) sub
group by id

Sample SQL Fiddle