Page 1 of 1

Equivalent of implode() purely in MySQL

Posted: Fri Jul 28, 2006 5:04 am
by Chris Corbyn
Hi,

I'm looking to pull data (just one column) from mysql which is over multiple rows.

e.g.

apple
banana
orange
pear

Now, I actually want the data in this format in just one row:

"apple,banana,orange,pear"

Does mysql offer some sort of "SELECT IMPLODE(',', my_column) AS foo" type of thing? I know I can do it with PHP but this a part of a much larger query so I'm trying to keep this all in a single query.

Cheers,

d11

Posted: Fri Jul 28, 2006 5:29 am
by JayBird
CONCAT_WS() maybe?

Posted: Fri Jul 28, 2006 5:59 am
by Chris Corbyn
Pimptastic wrote:CONCAT_WS() maybe?
I tried but it just sticks it with the column from one row only. I then tried:

Code: Select all

select concat_ws(', ', (select column_name from tbl_name)) as joined_string
But that gives an error that concat_ws() can't contain more than one row.

I've approached the problem from a different angle anyway and still got it in one query but I'll need to use PHP to implode thos columns.

If anybody happens to know the MySQL way to do it, it would be cool though, although I won't use it in this instance now.

Where's ~timvw? :)

Posted: Fri Jul 28, 2006 6:05 am
by JayBird
Im just throwing things at you here now but what does GROUP_CONCAT do?

http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html

Posted: Fri Jul 28, 2006 2:56 pm
by Chris Corbyn
FYI:

Code: Select all

select group_concat(column_name) from tbl_name
Yeilds a comma delimited string :)

Posted: Fri Jul 28, 2006 4:05 pm
by Benjamin
Maybe

Code: Select all

serialize()
would work for you when saving it, and unserialize() when pulling it back out.