3

I'm trying to find the MySQL equivalent of the PostgreSQL functions array and array_to_string and came across this post but asking for oracle9i which doesn't help me. I need to achieve this with MySQL but even Google can't seem to find any suitable answers.

So you don't have to read two posts, here is a repeat of the question:

In PostgreSQL, using the array and array_to_string functions can do the following:

Given the table "people":

id | name
---------
1  | bob
2  | alice
3  | jon

The SQL:

SELECT array_to_string(array(SELECT name FROM people), ',') AS names;

Will return:

names
-------------
bob,alice,jon

Anyone have any ideas how to achieve this in MySQL?

Community
  • 1
  • 1
GoneAway
  • 33
  • 1
  • 4

1 Answers1

8

Try GROUP_CONCAT . e.g:

SELECT GROUP_CONCAT(name) AS names FROM people GROUP BY id;
Chandu
  • 81,493
  • 19
  • 133
  • 134
  • @DangerPaws, See: http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat for more details. – Johan May 28 '11 at 15:01
  • Excellent! Thanks for that! Exactly what I needed :) @Johan Thanks for the link – GoneAway May 28 '11 at 15:13
  • I take it from the documentation that I can't put a SELECT statement inside the GROUP_CONCAT function? – GoneAway May 28 '11 at 15:24
  • 3
    @myself, correction you can put a SELECT statement inside the GROUP_CONCAT but need to wrap the SELECT statement in an extra pair of brackets like this: GROUP_CONCAT((SELECT...)) – GoneAway May 28 '11 at 15:39