query to group remarks column with sum of other column in oracle

lets assume i have following table “marksheet”:

userid | subject |  type     | marks | remarks
1        science    theory    35     good
1        science    practical 25     very good
1        computer  theory    50     very good
1        computer   practical 25     average

Now I want it like this:

userid  | subject | theory_marks | theory_remarks | practical_marks | practical_remarks
1        science     60              good              25                very good
1        computer    50              very good         15                average

I have tried this.

select userid, subject, sum(case when type = 'theory' then marks else 0) theory_marks, sum(case when type = 'practical' then marks else 0) practical_marks from marksheet group by userid, subject

Now, I want to add theory_remarks and practical_remarks as above (table2).

Thank you for any help.

Answer

Assuming userid,subject,type is unique

select userid, subject, 
   max(case when type = 'theory' then marks end) theory_marks, 
   max(case when type = 'practical' then marks end) practical_marks,
   max(case when type = 'theory' then remarks end) theory_remarks, 
   max(case when type = 'practical' then remarks end) practical_remarks    
from marksheet 
group by userid, subject