Vicky Vicky - 4 years ago 173
SQL Question

Denormalizing using query in Oracle

I've a table like this:

STU_NAME SUBJECT MARKS
--------- --------- ------
1 ENGLISH 90
1 TAMIL 80
1 MATHS 70
2 MATHS 70
2 TAMIL 80
2 ENGLISH 95


And the result should be like below:

STU_NAME MATHS_MARK ENGLISH_MARK TAMIL_MARK TOTAL_MARKS
--------- ----------- ------------ ----------- -------------
1 70 90 80 240
2 70 95 80 245


Can we achieve this with a query?

Answer Source

I find that the easiest way is to use conditional aggregation:

select stu_name,
       max(case when subject = 'MATHS' then Marks end) as Maths,
       max(case when subject = 'ENGLISH' then Marks end) as English,
       max(case when subject = 'TAMIL' then Marks end) as Tamil,
       sum(Marks) as Total
from t
group by stu_name;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download