MySQL Group by one column, but select all data

Question

I've got a database which contains lots of data. I would like to select all data, but Group by one column.

So for example:

column a | column b
example  | apple
example  | pear
example  | orange
example  | strawberry

In the example column A is a column which has duplicate values and needs to be grouped. Column B however has different values, and I would like to select all these values.

In the end, I would like to get a query that results in:

example
- apple
- pear
- orange
- strawberry

Where the values of column B are in a jQuery accordion.

I've tried and searched for a couple of hours but still haven't found a way to do this.

EDIT: I've manage to solve the problem using the answer Gordon provided. Using PHP I exploded the query results and with a while loop I collected all the data from the arrays.

It's working great now, thanks guys!

Answer 1

I think you can get what you want using group_concat():

select a, group_concat(b)
from t
group by a;

This will create a list of "b"s for each a. In your example:

example    apple,pear,orange,strawberry

You can change the separator using the SEPARATOR keyword.

EDIT:

You can use group_concat() multiple times:

select a, group_concat(b) as bs, group_concat(c) as cs
from t
group by a;

Or, combine it with concat():

select a, group_concat(concat(b, ':', 'c')) as bcs
from t
group by a;