Converting row values to columns in result using CASE statement [duplicate]

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.

Yogesh Sharma · Accepted Answer · 2018-09-28 09:59:30Z

4

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;

answered Sep 28, 2018 at 9:59

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting row values to columns in result using CASE statement [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related