MySQL Pivot Handling Values

Question

I have a table like the one bellow:

+------+------+------+-------+
| pkey | name | exam | score |
+------+------+------+-------+
|    1 | Bob  |  Math|    75 |
|    2 | Bob  |  Eng |    77 |
|    3 | Bob  |  Phy |    78 |
|    4 | Sue  |  Math|    80 |
|    5 | Sue  |  Eng |    90 |
|    6 | Sue  |  Phy |    97 |
|    7 | Suzy |  Math|    98 |
|    8 | Suzy |  Eng |    99 |
|    9 | Suzy |  Phy |    99 |
+------+------+------+-------+

And I want to make a query to pivot it into:

+------+------+------+-------+
| Name | Math |  Phy |  Eng  | 
+------+------+------+-------+ 
|  Bob | 75   |  78  |    77 |
|  Sue | 80   |  97  |    90 |
|  Suzy| 90   |  99  |    99 |
+------+------+------+-------+

The problem is, the discipline of the exam can have like 500 different values and I can't define them all as they might change eventually and start having 600 different ones instead.

Is it possible to create a query that will read the value of the exam and create a new column to display the values of it?

Say for example they add Hist to the values of exam and say Suzy got a 100, it would then be like this:

+------+------+------+-------+-----+
| Name | Math |  Phy |  Eng  | Hist|
+------+------+------+-------+-----+
|  Bob | 75   |  78  |    77 |     |
|  Sue | 80   |  97  |    90 |     |
|  Suzy| 90   |  99  |    99 | 100 |
+------+------+------+-------+-----+

Thanks in advance

Gordon Linoff · Accepted Answer · 2014-05-20 16:25:11Z

1

A SQL query has to have a well-defined set of columns. You can do what you want with a prepare statement and dynamic SQL.

Another approach would be to create a string of the possible values, such as Math:75, Phy78, Eng:77. If this works for you:

select name, group_concat(exam, ':', score separator ', ')
from table t
group by name;

answered May 20, 2014 at 16:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

MySQL Pivot Handling Values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related