pivot/unpivot oracle sql query with more than 2 resulting columns

Question

I have a query whose output ina row are as follows:

Math MathStartDate  Science ScienceStartDate    Comp    CompStartDate   Hist    HistStartDate
12      11/12/2020  13      11/02/2020          6       11/01/2020      45      11/01/2020

I want the result to look like this

Code    Value1  Value2
Math    12      11/12/2020
Science 13      11/02/2020
Comp    6       11/01/2020
Hist    45      11/01/2020

To get the result like above i am using unpivot as follows:

select * from (
select 
Math,
MathStartDate,
Science,
ScienceStartDate,
Comp,
CompStartDate,
Hist,
HistStartDate
from subjects vw, students s
where s.id = vw.id 
and id = 56
)
UNPIVOT INCLUDE NULLS(value1 FOR code in(Math,
Science,
Comp,
Hist));

The ouput of this looks like,

MathStartDate ScienceStartDate CompStartDate HistStartDate code     value1
11/12/2020      11/02/2020      11/01/2020      11/01/2020  Math    12  
11/12/2020      11/02/2020      11/01/2020      11/01/2020  Science 13
11/12/2020      11/02/2020      11/01/2020      11/01/2020  Comp    6
11/12/2020      11/02/2020      11/01/2020      11/01/2020  Hist    45

How do I get the Dates to be aligned so I get desired output?

Answer 1

You can unpivot multiple columns:

...
UNPIVOT INCLUDE NULLS (
  (value1, startdate)
  FOR code in (
    (Math, MathStartDate) as 'Math',
    (Science, ScienceStartDate) as 'Science',
    (Comp, CompStartDate) as 'Comp',
    (Hist, HistStartDate) as 'Hist'
  )
);

CODE    | VALUE1 | STARTDATE
------- | ------ | ---------
Math    |     12 | 12-NOV-20
Science |     13 | 02-NOV-20
Comp    |      6 | 01-NOV-20
Hist    |     45 | 01-NOV-20

db<>fiddle