MySQL merge duplicate rows

Question

I'm struggling with a query I have to make for a program:

This is the structure of my table (example column names):

One ID and name can have multiple Coefficients, like:

ID-Name-Coefficient
1-NameHere-0.5
1-NameHere-0.6
1-NameHere-0.7

This is how I would like my query result to look like:

1-NameHere-0.5-0.6-0.7

So all the duplicate coefficnents, I want in seperate columns, but all in one row.

What would be the best way to achieve this query?

This is called a pivot. MySQL doesn't have built in functionality for this. Without any research effort, I don't think anyone will want to write the code for you. — Kermit, Apr 18 '13 at 16:40
if the number of coefficients are known this can be accomplished with inner joins based on the known quantity. If they are not limited, you're looking at some dynamic SQL. See: http://stackoverflow.com/questions/4078983/is-there-a-way-to-pivot-rows-to-columns-in-mysql-without-using-case for more info — xQbert, Apr 18 '13 at 16:43

score 2 · Answer 1 · answered Apr 18 '13 at 16:41

You can't have variable column count in SQL, but you can concatenate the values in one column:

SELECT  id, name, GROUP_CONCAT(coefficient)
FROM    mytable
GROUP BY
        id, name

This will return something like

0.5,0.6,0.7

in the third column which you can later parse on the client.

1 Answers1