Mysql:How to concatenate more than one row into single row with diffrent field name?

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.