Delete duplicate records while updating fields to a specific record in MYSQL-CodePudding

I have a table which has duplicate of entries by code_number.

Table: sheets

 id  |  code_number | animal     |  location
  1      001          cow           loc1
  2      002          dog           loc2
  3      003          goat          loc3
  4      003          sheep         loc4
  5      003          elephant      loc5
  6      002          bird          loc6

I am about to delete duplicate records and put all the group fields into a single entry.

Based on the table data above, my expected result is:

 code_number  |   animal              | location
    001       |     cow               |    loc1
    002       |  dog, bird            |   loc2, loc6, 
    003       | goat, sheep, elephant | loc3, loc4, loc5

I can only produce the deleting of duplicate entries and retain a single entry. But, I don't know how to update/copy the record into a single record while deleting the rest duplicate entries.

DELETE n1 FROM sheets n1, sheets n2
WHERE n1.id > n2.id AND n1.code_number = n2.code_number;

How can I do this in just a single query?

CodePudding user response：

Try this below code for preparing comma separated value.

SELECT code_number, group_concat(animal) animal, group_concat(location) location FROM sheets GROUP BY code_number

Use DISTINCT keyword inside group_concat function if same value exists multiple time of a code number.

Insert this query result into a different tables.