id Subject mark Year
-------------------------
1 Maths 32 2008
1 Science 40 2009
1 Science 45 2008
1 English 50 2009
1 English 60 2008
I am looking for a result like this:
id Maths Science English
-----------------------------
1 32 40 & 45 50 & 60
Please advise. I am using MySQL.
For the first step, take a look at the group_concat() functiopn in mysql, it may be of some help
SELECT `id`,
`subject`,
group_concat( `mark`
ORDER BY `year` DESC
SEPARATOR ' & '
) marks
FROM `subjects`
GROUP BY `id`,
`subject`
UPDATE Managed to get as far as
SELECT `id`,
IF (`subject` = 'English', `marks`, NULL) AS English,
IF (`subject` = 'Maths', `marks`, NULL) AS Maths,
IF (`subject` = 'Science', `marks`, NULL) AS Science
FROM ( SELECT `id`,
`subject`,
group_concat( `mark`
ORDER BY `year` DESC
SEPARATOR ' & '
) marks
FROM `subjects`
GROUP BY `id`,
`subject`
) x
GROUP BY `id`,
`subject`
which returns
+----+---------+-------+---------+
| id | English | Maths | Science |
+----+---------+-------+---------+
| 1 | 50 & 60 | NULL | NULL |
| 1 | NULL | 32 | NULL |
| 1 | NULL | NULL | 40 & 45 |
+----+---------+-------+---------+
3 rows in set (0.01 sec)
but can't quite get that last grouping right to return it all as a single row. Don't know if anybody else can help me get the final step right when taking this approach to the problem.... ordinarily, I'd return the result to PHP "as is", and do the final grouping there; but a complete MySQL solution would be interesting.
As @Mark suggested, GROUP_CONCAT() can give you the following result:
SELECT id, subject, GROUP_CONCAT(mark SEPARATOR ' & ') marks
FROM results
GROUP BY id, subject;
+------+---------+---------+
| id | subject | marks |
+------+---------+---------+
| 1 | English | 50 & 60 |
| 1 | Maths | 32 |
| 1 | Science | 40 & 45 |
+------+---------+---------+
3 rows in set (0.00 sec)
From this test case:
CREATE TABLE results (id int, subject varchar(10), mark int);
INSERT INTO results VALUES (1, 'Maths', 32);
INSERT INTO results VALUES (1, 'Science', 40);
INSERT INTO results VALUES (1, 'Science', 45);
INSERT INTO results VALUES (1, 'English', 50);
INSERT INTO results VALUES (1, 'English', 60);
However another way to tackle the problem would be by using as sub query for each subject:
SELECT r.id,
(SELECT GROUP_CONCAT(r_eng.mark SEPARATOR ' & ')
FROM results r_eng
WHERE r_eng.subject = 'English' AND r_eng.id = r.id) English,
(SELECT GROUP_CONCAT(r_eng.mark SEPARATOR ' & ')
FROM results r_eng
WHERE r_eng.subject = 'Maths' AND r_eng.id = r.id) Maths,
(SELECT GROUP_CONCAT(r_eng.mark SEPARATOR ' & ')
FROM results r_eng
WHERE r_eng.subject = 'Science' AND r_eng.id = r.id) Science
FROM results r
GROUP BY r.id;
Which will give the following result:
+------+---------+-------+---------+
| id | English | Maths | Science |
+------+---------+-------+---------+
| 1 | 50 & 60 | 32 | 40 & 45 |
+------+---------+-------+---------+
1 row in set (0.01 sec)
UPDATE:
Further to the comments, it looks like you need to take the year field in consideration. Luckily the GROUP_CONCAT() function takes an ORDER BY clause which we can use. Let's start from a new test case with the year field:
CREATE TABLE results (id int, subject varchar(10), mark int, year int);
INSERT INTO results VALUES (1, 'Maths', 32, 2008);
INSERT INTO results VALUES (1, 'Science', 40, 2009);
INSERT INTO results VALUES (1, 'Science', 45, 2008);
INSERT INTO results VALUES (1, 'English', 50, 2009);
INSERT INTO results VALUES (1, 'English', 60, 2008);
SELECT * FROM results;
+------+---------+------+------+
| id | subject | mark | year |
+------+---------+------+------+
| 1 | Maths | 32 | 2008 |
| 1 | Science | 40 | 2009 |
| 1 | Science | 45 | 2008 |
| 1 | English | 50 | 2009 |
| 1 | English | 60 | 2008 |
+------+---------+------+------+
5 rows in set (0.00 sec)
Then we would be able to use the GROUP_CONCAT() function with the ORDER BY clause as follows:
SELECT id,
subject,
GROUP_CONCAT(mark ORDER BY year SEPARATOR ' & ') marks,
GROUP_CONCAT(year ORDER BY year SEPARATOR ' & ') years
FROM results
GROUP BY id, subject;
+------+---------+---------+-------------+
| id | subject | marks | years |
+------+---------+---------+-------------+
| 1 | English | 60 & 50 | 2008 & 2009 |
| 1 | Maths | 32 | 2008 |
| 1 | Science | 45 & 40 | 2008 & 2009 |
+------+---------+---------+-------------+
3 rows in set (0.00 sec)
Finally, to GROUP BY everything in one horizontal row, we can use the subquery technique that we used in the earlier example:
SELECT r.id,
(SELECT GROUP_CONCAT(r_eng.mark ORDER BY year SEPARATOR ' & ')
FROM results r_eng
WHERE r_eng.subject = 'English' AND r_eng.id = r.id) English,
(SELECT GROUP_CONCAT(r_eng.year ORDER BY year SEPARATOR ' & ')
FROM results r_eng
WHERE r_eng.subject = 'English' AND r_eng.id = r.id) Years_English,
(SELECT GROUP_CONCAT(r_eng.mark ORDER BY year SEPARATOR ' & ')
FROM results r_eng
WHERE r_eng.subject = 'Maths' AND r_eng.id = r.id) Maths,
(SELECT GROUP_CONCAT(r_eng.year ORDER BY year SEPARATOR ' & ')
FROM results r_eng
WHERE r_eng.subject = 'Maths' AND r_eng.id = r.id) Years_Maths,
(SELECT GROUP_CONCAT(r_eng.mark ORDER BY year SEPARATOR ' & ')
FROM results r_eng
WHERE r_eng.subject = 'Science' AND r_eng.id = r.id) Science,
(SELECT GROUP_CONCAT(r_eng.year ORDER BY year SEPARATOR ' & ')
FROM results r_eng
WHERE r_eng.subject = 'Science' AND r_eng.id = r.id) Years_Science
FROM results r
GROUP BY r.id;
Which will return the following:
+----+---------+---------------+-------+-------------+---------+---------------+
| id | English | Years_English | Maths | Years_Maths | Science | Years_Science |
+----+---------+---------------+-------+-------------+---------+---------------+
| 1 | 60 & 50 | 2008 & 2009 | 32 | 2008 | 45 & 40 | 2008 & 2009 |
+----+---------+---------------+-------+-------------+---------+---------------+
1 row in set (0.01 sec)
If you wanted the marks and the years ordered in descending order, you could simply add the DESC keyword after each ORDER BY year.