Pivoting data in MySQL.

Question

I'm sure this sort of question has come up before but I've been searching and can't find anything similar to what I need.

Edit: so after some reading this looks like it falls under pivots and uses group concat. If anybody has any insight id really appreciate it.

I have 3 tables (unnecessary fields & data stripped out for simplicity):

Students
id       name
------------------
1        John
2        Jane



Tests
id     name
------------------
1      Test1
2      Test2



Results
id    test_id     student_id   result
--------------------------------------
1     1           1            90
2     1           2            70
3     2           1            50
4     2           2            95

What I want is to be able to produce a table like this:

Name     Average    Test1    Test2
-----------------------------------
John     70         90        50
Jane     92.5       70        95

I know how to get the average, and I'm sure I could do this with an ugly set of loops and php logic but I'd like to get the most efficient solution. Any help is greatly appreciated.

Ethan Clark · Accepted Answer · 2012-06-02 01:22:50Z

2

SELECT
    s.name,
    avg(r.result) AS average,
    t1.result AS test1,
    t2.result AS test2
FROM
    students s,
    results r,
    results t1,
    results t2
WHERE
    r.student_id = s.id AND
    t1.test_id = 1 AND
    t1.student_id = s.id AND
    t2.test_id = 2 AND
    t2.student_id = s.id
GROUP BY s.id;

+------+---------+-------+-------+
| name | average | test1 | test2 |
+------+---------+-------+-------+
| John |      70 |    90 |    50 | 
| Jane |    82.5 |    70 |    95 | 
+------+---------+-------+-------+

Edit: You can't really make the test columns dynamically based on the contents of the tests table. You can pick specific values to use as columns though.

edited Jun 2, 2012 at 1:22

answered Jun 2, 2012 at 1:15

Ethan Clark

363 bronze badges

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

Comments

Isaac Hildebrandt · Accepted Answer · 2012-06-01 19:12:38Z

0

You'll want to look at the AVG function.

answered Jun 1, 2012 at 19:12

Isaac Hildebrandt

1,0265 silver badges16 bronze badges

Comments

jcho360 · Accepted Answer · 2012-06-01 19:12:55Z

0

Try This:

mysql> Select s.Name as Name, avg(r.result) as Average 
from result as r join Student as s 
on r.id=s.id group by (r.id)

you will have something like this:

Name     Average 
-----------------
John     70    
Jane     92.5

You can't put a row as a column, imagine that John have 100000 times the test1, it will no be possible to put it as column.

answered Jun 1, 2012 at 19:12

jcho360

3,7491 gold badge18 silver badges25 bronze badges

2 Comments

Mcg1978 Over a year ago

I've kept searching and it looks possible. It's something to do with pivot tables and the group concat function.

jcho360 Over a year ago

@Mcg1978 You can make it dynamic if you create some kind of procedure or function, but remember for each row as column you need a "JOIN" that will affect performance.

Collectives™ on Stack Overflow

Pivoting data in MySQL.

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related