user3278770 user3278770 - 6 months ago 9
MySQL Question

mysql how to merge similar records within a table

Table:


ID / Name / Type / Taste / Score

1 / Cox / apple / good / NULL

2 / Cox / pear / NULL / 6

3 / Bob / apple / great / NULL

4 / Rod / pear / NULL / 9

5 / King / pear / NULL / 3

6 / King / apple / bad / NULL


As you can see:
'apples' have a 'Taste' value but no 'Score'
'pears' have no 'Taste' value but have a 'Score'.

I want to merge them together so that if two records have the same 'Name' then they become one record with the 'Taste' value taken from the Apple and 'Score' taken from the Pear.

When the above table is used, the output I want is like this:

ID / Name / Type / Taste / Score

1 / Cox / apple / good / 6

3 / Bob / apple / great / NULL

4 / Rod / pear / NULL / 9

5 / King / pear / bad / 3

I am using MySQL and PHP. I guess it can be done using either.

Answer

You can do it using conditional aggregation :

SELECT min(t.id),t.name,min(t.Type),
       MAX(CASE WHEN t.type = 'apple' then t.taste END) as taste,
       MAX(CASE WHEN t.type = 'pear' then t.Score END) as Score 
FROM YourTable t
GROUP BY t.name