I am trying to unpivot columns to rows so that they are grouped by a unit number that I was hoping to construct in the unpivot function
To simplify the explanation see the example query below:
select
'123456789' student
,'01/Jul/2020' unit_1_date
,'Mathematics 'unit_1_subject
,'01/Aug/2020' unit_2_date
,'English 'unit_2_subject
from
dual
which outputs five columns:
student unit_1_date unit_1_subject unit_2_date unit_2_subject
123456789 01/Jul/2020 Mathematics 01/Aug/2020 English
I wish to unpivot these columns so they can be grouped by a unit number and display like this:
student unit_number unit_date unit_subject
123456789 1 01/Jul/2020 Mathematics
123456789 2 01/Aug/2020 English
I have tried to do this with the unpivot function as shown below:
select * from
(select
'123456789' student
,'01/Jul/2020' unit_1_date
,'Mathematics 'unit_1_subject
,'01/Aug/2020' unit_2_date
,'English 'unit_2_subject
from
dual) units
unpivot(unit_date for unit_number in(
unit_1_subject as '1',
unit_1_date as '1',
unit_2_subject as '2',
unit_2_date as '2'
))
which is outputting the date like this:
Student Unit number Unit_date
123456789 1 Mathematics
123456789 1 01/Jul/2020
123456789 2 English
123456789 2 01/Aug/2020
I'm unsure how to go about grouping two of the columns so they are grouped by a unit number. What's the best way to do this? Is this achievable with the UNPIVOT function?
Thanks
You can use the hierarchy query as follows:
SQL> SELECT
2 STUDENT,
3 LEVEL AS UNIT,
4 CASE WHEN LEVEL = 1 THEN UNIT_1_DATE ELSE UNIT_2_DATE END AS UNIT_DATE,
5 CASE WHEN LEVEL = 1 THEN UNIT_1_SUBJECT ELSE UNIT_2_SUBJECT END AS SUBJECT
6 FROM
7 ( SELECT
8 '123456789' STUDENT,
9 '01/Jul/2020' UNIT_1_DATE,
10 'Mathematics ' UNIT_1_SUBJECT,
11 '01/Aug/2020' UNIT_2_DATE,
12 'English ' UNIT_2_SUBJECT
13 FROM DUAL
14 ) CONNECT BY LEVEL <= 2;
STUDENT UNIT UNIT_DATE SUBJECT
--------- ---------- ----------- ------------
123456789 1 01/Jul/2020 Mathematics
123456789 2 01/Aug/2020 English
SQL>