Join the records with sql?

I have a table with those 5 rows.

code | type_id | status
-----+--------+--------
123  | 123456 | DONE
123  | 456789 | DONE
321  | 654321 | DONE
321  | 897321 | DONE
456  | 999888 | DONE
456  | 777666 | FAIL

And I want to change it to below with DONE only.

code | type_id1 | type_id2
-----+----------+---------
123  | 123456   | 456789
321  | 654321   | 897321
456  | 999888   | null

How can I join them to show the result?

Answer

If I understand correctly that you want one row per code, you can use aggregation:

select code,
       min(type_id) as type_id1,
       (case when min(type_id) <> max(type_id) then max(type_id) end) as type_id2
from t
where status = 'DONE'
group by code;

Note that SQL tables represent unordered sets. With your sample data, there is no way to preserve “the original” order of the values, because that is undefined — unless another column specifies that ordering.