Converting row values to columns in result using CASE statement

Question

I have a database table which has multiple records as below.

I'm trying to convert row data into column result using CASE WHEN statements to make some important information.

Student

-------------------------
|ID|Name   |Subject|Mark|
|01|Ishara |Maths  |40  |
|01|Ishara |Science|60  |
|02|Tharaka|Maths  |60  |
|03|Rukshan|Science|20  |
|04|Duminda|Science|60  |
------------------------

I need to get result set as below,

-------------------------------------
ID|Name    |Maths Mark| Science Mark|
01|Ishara  |40        |60           |
02|Tharaka |60        |Null         |
03|Rukshan |Null      |20           |
04|Duminda |Null      |60           |
-------------------------------------

Appreciate if anybody can help to do this task by using simple SQL query.

Answer 1

You can do conditional aggregation with help of case expression :

select id, name, 
       max(case when subject = 'Maths' then mark end) as Maths_mark, 
       max(case when subject = 'Science' then mark end) as Science_mark,
       . . .
from student s 
group by id, name;