GROUP_CONTACT another mysql magic

September 20, 2011 § Leave a comment

I was wondering how to get repeated rows in to columns. For eg I have to show marks scored by a student for each subject on a specific Term. Initially it displayed as

Subject | Exam | Mark | Term
——————————
Music | TEST1 | 85 | One
Dance | TEST1 | 90 | One
Music | TEST2 | 80 | One

I want it as follows:

Subject | Exam | Mark | Term
——————————————
Music | TEST1, TEST 2 | 85, 80 | One
Dance | TEST1 | 90 | One

I got this solutions


SELECT subject.name, group_concat( exam.name ) AS exam, group_concat( mark.marks ) AS marks
FROM marks mark
LEFT JOIN subjects subject ON xxxxxxxxxxxxxxxxxxxxx
LEFT JOIN marks mark ON xxxxxxxxxxxxxxxxxxxx
LEFT JOIN exams exam ON xxxxxxxxxxxxxxxxxxxx
LEFT JOIN terms_info term ON xxxxxxxxxxxxxxxxxxxxx
WHERE mark.stud_id =69
GROUP BY subject.name
ORDER BY exam.name

Summary
Use group_concat on the field which should be merged (in my case it is exam and mark ) then use a GROUP BY on respective field

Where Am I?

You are currently viewing the archives for September, 2011 at ARP's Web Blog.