Returning results as in one field instead of several rows (MySQL)

[legendx]

I'm not looking for an exact query, rather a direction to look for.

What I'm looking to do is go from a result set like this:

1 | Blue
2 | Green
3 | Red
4 | Purple

And instead have MySQL return this sort of result:

1 | Blue Green Red Purple

I think variables might be a way to achieve this but that could be overkill. Is there an easy way to do this?

[cs3mw]

what are you wanting to do with this? You could do this with your programming language i.e. PHP or ASP! Does it have to come directly from the database?

[legendx]

I know I can do this with PHP but I'm going to be running this sort of query several hundred times and it will be faster if the processing is done on the db server rather than the PHP interpreter.

[cs3mw]

Im not aware of a way to do this I would basically just do a select statement and then use mysql_fetch_array, but if thats no good then im out of ideas im afraid!

Mike

[legendx]

Im not aware of a way to do this I would basically just do a select statement and then use mysql_fetch_array, but if thats no good then im out of ideas im afraid!

Mike
Thanks for trying Mike :)

[chazzy]

what if you use a concat?

[legendx]

what if you use a concat?

The way I normally use CONCAT() is when you want to join 2 fields of the same row (i.e. CONCTAC(first_name, ' ', last_name)). I thought the same thing too but I'm trying to join fields from multiple rows.

[chazzy]

maybe a cursor then too?? but even a concat, i think your argument for wanting to do it this way is flawed.

[legendx]

maybe a cursor then too?? but even a concat, i think your argument for wanting to do it this way is flawed.

I'll be more specific then :)

I have an events table with a many to many relationship to a categories table.. and all I want is an events title and list of categories. Right now the only way I know of to accomplish this is by querying the db with a join and getting a result something like:

ID | Title | Category
1 | Dinner | 2
1 | Dinner | 3
1 | Dinner | 6
2 | Lunch | 1
2 | Lunch | 5

What I want is

ID | Title | Category
1 | Dinner | 2 3 6
2 | Lunch | 1 5

I know I could mash all this together using PHP but that seems unnecessary with all the power that SQL has.

[cs3mw]

Unfortunately though the rules of the relational database permits you from doing such an action. I would strongly recommend sending your results to an array then looping through each of them.

[chazzy]

you're working on a bad assumption then - performance wise, SQL is very slow at numerical and string manipulation.