CODEWITHSUNDEEP
mysqlsql
Shan
Shan

Reputation: 2832

How to pivot some values in a MySQL table and display separately

I have the following table structure

+---------+----------+-------+------------+------------+
| Student | Subject  | Grade |   Marks    | YearID     |
+---------+----------+-------+------------+------------+
| John    | English  | A     |         80 |         15 |
| John    | French   | C     |         94 |         15 |
| Mike    | English  | A     |         31 |         15 |
| Mike    | French   | C     |         73 |         15 |
+---------+----------+------+------------+------------+

My problem is I need something like this in MySQL

 +---------+----------+------+----------+
 | Student | English  |French | YearID  |
 | John    | 80       | 94    | 15      |
 +---------+----------+------+----------+

How do this in a MySQL script

Upvotes: 0

Views: 32

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

You have rows per Student + Subject + YearID in the table but you want result rows per Student + YearID only. This means you want to aggregate the data and group by Student + YearID. Then use CASE WHEN to access the correct subject:

select 
  student, 
  case when subject = 'English' then marks end as english, 
  case when subject = 'French' then marks end as french, 
  yearid
from mytable
group by student, yearid;

This gives you a row per Student + YearID and picks an English record for the English column and a french record for the French column. As there can be only one English and one French record per Student + YearID in the table, we don't even need an aggregate function (such as SUM or MAX) here.

Upvotes: 1

Related Questions