MySQL merge duplicate rows [closed]

Question

Closed. This question is off-topic. It is not currently accepting answers.

Want to improve this question? Update the question so it's on-topic for Stack Overflow.

Closed 12 years ago.

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
– Kermit, Commented Apr 18, 2013 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: stackoverflow.com/questions/4078983/… for more info — xQbert
– xQbert, Commented Apr 18, 2013 at 16:43

Quassnoi · Accepted Answer · 2013-04-18 16:41:31Z

2

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.

answered Apr 18, 2013 at 16:41

Quassnoi

428k94 gold badges628 silver badges624 bronze badges

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

1 Answer 1