How to transpose in Oracle?

Question

I have a table:

And I want to make it like this in Oracle SQL:

How can I write code in SQL?

Answer 1

You can use the conditional aggregation as follows:

select subjid,
       max(case when status = 'most recent dose' then date end) as most_recent_dose
       max(case when status = 'second most recent dose' then date end) as second_most_recent_dose
       max(case when status = 'dosing' then date end) as dosing
  from your_table
group by subjid