Rows that are similar in all but one column

I'm not sure what this is called but it happens all the time that you get a resultset like:

(id,name,age,favorite)
0,John Smith,21,Pepperoni 
1,John Smith,21,Ham
2,John Smith,21,Olives
3,John Doe,54,开发者_JAVA技巧Tomatoes
4,John Doe,54,Potatoes 

Now when you're iterating over this resultset, how can you concat the last column on rows that have similar name and ages? To get a result like John Smith,21,"Pepperoni,Ham,Olives". And what is this action called in general? I'm using JDBC-MySQL if that matters.

Thanks

This action could be done with GROUP_CONCAT in MySQL :

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

SELECT name, age, GROUP_CONCAT(favorite SEPARATOR ',')
FROM myTable
GROUP BY name, age

Be careful, GROUP_CONCAT is only in MySQL and not a standard SQL.

You could not do that in other DBMS with a query.

On MySQL, you could do that with group_concat:

select 
  id,
  name,
  age,
  group_concat(favorite separator ',')
from 
  YourTable
group by
  id,
  name,
  age

Is your data normalised? If it is, and your favorites are stored in a separate table, use GROUP_CONCAT to concatenate them.

Simply:

select name, age, group_concat(favorite) group by name, age;

You may want group_concat(distinct favorite), or to specify an order by. (comma is the default separator.)

group_concat values will be truncated at a server-variable determined length, but that doesn't look to be an issue in this case.