Transposing SQL rows data to column

Question

In SQL we need to transform a table in the following way:

Table1:

+-----+---------+-----------+
| ID  | insured |    DOD    |
+-----+---------+-----------+
| 123 | Pam     | 6/18/2013 |
| 123 | Nam     | 2/12/2010 |
| 123 | Tam     | 2/10/2013 |
| 456 | Jessi   | 4/6/2003  |
| 457 | Ron     | 4/10/2010 |
| 457 | Tom     | 5/5/2008  |
+-----+---------+-----------+

Desired output table:

+-----+---------+-----------+-----------+-----------+
| ID  | insured |   DOD1    |   DOD2    |   DOD3    |
+-----+---------+-----------+-----------+-----------+
| 123 | Pam     | 6/18/2013 | 2/12/2010 | 2/10/2013 |
| 456 | Jessi   | 4/6/2003  | null      | null      |
| 457 | Ron     | 4/10/2010 | 5/5/2008  | null      |
+-----+---------+-----------+-----------+-----------+

I have seen somewhere that we can use pivot and unpivot, but I am not sure how can I use it here.

Your help is much appreciated.

Answer 1

Assuming that you really want -- or can accept -- the dates in descending order, then you can use conditional aggregation for this:

select id,
       max(case when seqnum = 1 then insured end) as insured,
       max(case when seqnum = 1 then dod end) as dod_1,
       max(case when seqnum = 2 then dod end) as dod_2,
       max(case when seqnum = 3 then dod end) as dod_3
from (select t.*,
             row_number() over (partition by id order by dod desc) as seqnum
      from t
     ) t
group by id;

If you want to preserve the original ordering, then your question does not have enough information. If you have a column with the ordering, that can be used for row_number().