Membuat Pivot dalam MySQL

Buat Database
CREATE TABLE exams ( pkey int(11) NOT NULL auto_increment,name varchar(15),

exam int, score int,PRIMARY KEY  (pkey));

Isi Database

insert into exams (name,exam,score) values ('Bob',1,75);

insert into exams (name,exam,score) values ('Bob',2,77);

insert into exams (name,exam,score) values ('Bob',3,78);

insert into exams (name,exam,score) values ('Bob',4,80);

insert into exams (name,exam,score) values ('Sue',1,90);

insert into exams (name,exam,score) values ('Sue',2,97);

insert into exams (name,exam,score) values ('Sue',3,98);

insert into exams (name,exam,score) values ('Sue',4,99);

Tampilkan Database

mysql> select * from exams;

+------+------+------+-------+
| pkey | name | exam | score |
+------+------+------+-------+
|    1 | Bob  |    1 |    75 |
|    2 | Bob  |    2 |    77 |
|    3 | Bob  |    3 |    78 |
|    4 | Bob  |    4 |    80 |
|    5 | Sue  |    1 |    90 |
|    6 | Sue  |    2 |    97 |
|    7 | Sue  |    3 |    98 |
|    8 | Sue  |    4 |    99 |
+------+------+------+-------+
8 rows in set (0.00 sec)

Buat Query

mysql> select name,sum(score*(1-abs(sign(exam-1)))) as exam1,

sum(score*(1-abs(sign(exam-2)))) as exam2,

sum(score*(1-abs(sign(exam-3)))) as exam3,

sum(score*(1-abs(sign(exam-4)))) as exam4

from exams group by name;

+------+-------+-------+-------+-------+

 name | exam1 | exam2 | exam3 | exam4 |

+------+-------+-------+-------+-------+

 Bob  |    75 |    77 |    78 |    80 |

| Sue  |    90 |    97 |    98 |    99 |

+------+-------+-------+-------+-------+

2 rows in set (0.00 sec)