oracle - sql grouping grades

Question

I have a table for subjects as follows:

id  Subject Grade   Ext
100 Math    6       +
100 Science 4       -
100 Hist    3       
100 Geo     2       +
100 CompSi  1       

I am expecting output per student in a class(id = 100) as follows:

Grade   Ext StudentGrade
6       +       1
6               0
6       -       0
5       +       0
5               0
5       -       0
4       +       0
4               0
4       -       1
3       +       0
3               1
3       -       0
2       +       1
2               0
2       -       0
1       +       0
1               1
1       -       0

I would want this done on oracle/sql rather than UI. Any inputs please.

question from:https://stackoverflow.com/questions/65891377/sql-grouping-grades

Answer 1

You should generate rows first, before join them with your table like below. I use the with clause here to generate the 18 rows in your sample.

with rws (grade, ext) as (
select ceil(level/3), decode(mod(level, 3), 0, '+', 1, '-', null)
from dual 
connect by level <= 3 * 6
)
select r.grade, r.ext, nvl2(t.Ext, 1, 0) studentGrade 
from rws r
left join your_table t
on t.Grade = r.Grade and decode(t.Ext, r.Ext, 1, 0) = 1
order by 1 desc, decode(r.ext, null, 2, '-', 3, '+', 1)