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

Advertisements

Tagged:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

What’s this?

You are currently reading GROUP_CONTACT another mysql magic at ARP's Web Blog.

meta

%d bloggers like this: