Combine 2 rows into one result MySQL

Question

I have checked out some other questions, and I cant seem to find any that answer my specific question.

The following table is the result of this query:

SELECT teacher.teacherID, unitName FROM teacher INNER JOIN unit ON teacher.teacherID=unit.teacherID ORDER BY teacherID ASC

Table

What I actually need the result to display is this:

Table 2

Is there a way to modify my current query so that this can be achieved? I wasn't sure if this was possible in MySQL at all, but as I have learned in the past, you should never underestimate the power of a query! (this was discovered after spending time using messy nested loops trying to manipulate an array, instead i managed to do it all in a query)

EDIT: SQL Fiddle

Thanks, and I would appreciate any help!

It would be easier for us to help you if you set up a SQL Fiddle — John Conde
– John Conde, Commented Apr 19, 2013 at 19:33

alexn · Accepted Answer · 2013-04-19 19:33:58Z

4

Yes, you need to use a GROUP BY and GROUP_CONCAT:

SELECT teacher.teacherID, GROUP_CONCAT(unitName)
FROM teacher
INNER JOIN unit ON teacher.teacherID=unit.teacherID
GROUP BY teacher.teacherID
ORDER BY teacherID ASC

This query works directly in your SQL Fiddle.

answered Apr 19, 2013 at 19:33

alexn

59.2k14 gold badges114 silver badges146 bronze badges

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

1 Comment

Dan Mason Over a year ago

Excellent, thankyou this answer worked perfectly! I will accept this as soon as stackoverflow lets me!

diamondsea · Accepted Answer · 2013-04-19 19:55:20Z

2

A slight tweak to alexn's answer to match your formatting with spaces after the comma:

SELECT teacher.teacherID, GROUP_CONCAT(unitName SEPARATOR ', ')
FROM teacher
INNER JOIN unit ON teacher.teacherID=unit.teacherID
GROUP BY teacher.teacherID
ORDER BY teacherID ASC

answered Apr 19, 2013 at 19:55

diamondsea

3,0701 gold badge14 silver badges9 bronze badges

1 Comment

Diana Over a year ago

how would you do this but getting rid of duplicate unitNames ?

Collectives™ on Stack Overflow

Combine 2 rows into one result MySQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related