MYSQL: combine duplicate rows

Question

I have a problem when I need to combine a bunch of duplicate data. I start with a table that has been combined from two different sources. An example of data might be:

aID, bID, value1, value2, value3, value4

The problem is that there are situations when aID matches BID, but only one of the other fields is filled in:

1, 1, samedata, null, null, 1
1, 1, samedata, red, null, null
1, 1, samedata, null, htmldata, null

I am looking for a way to display the entire row of data, but roll up all the values ​​that are not null, so there is one row for each match identifier. I tried Group By and Group_Concat, but have not yet found the secret sauce.

The 1 line I would like to get from the above example would be:

1, 1, samedata, red, htmldata, 1

Any ideas?

Answer 1

One trick is to use MAX or MIN , as they would prefer a non-zero value over a zero value:

 SELECT aID, bID, MAX(value1) AS value1, MAX(value2) AS value2, MAX(value3) AS value3, MAX(value4) AS value4 FROM ... GROUP BY aID, bID ; 

(When I use MAX for this purpose, I usually include a comment noting this, as it does not immediately become apparent to the casual reader.)

Answer 2

Try the following:

 Select aID, bId, value1, value2, value3 from table group by CONCAT(ifnull(aID,-1),ifnull(bId)