CODEWITHSUNDEEP
phpmysql
user3278770
user3278770

Reputation: 159

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.

Upvotes: 3

Views: 58

Answers (1)

sagi
sagi

Reputation: 40481

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

Upvotes: 2

Related Questions