Concatenating multiple rows to one field?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hi there!

I got a slight problem with a web application, its supposed to several
entries for a certain ID, and display them as one string.


ID | Name
1  | Jack
1  | Peter
2  | John
2  | Mark
2  | Ellis

Expected Result for ID 1 should be "Jack, Peter" for example, or "John,
Mark, Ellis" for 2.
Least problem would be clipping off a trainling or leading ",", but the
main problem is a SELECT statement to get them all in one go. Else I'd
have to select every Name per ID, and concatenate them in PHP or
elsewhere. Given its around 100 IDs, I'd have to do like 100 querys to
get the names, *in addition* to the Querys I need before and after.

I want to avoid that, but I couldnt find a simple solution that works
in MySQL 4.1 and above, aswell as it mustn't involve Stored Functions
or something, because I got no access to the mysqld itself, to add the
funcs as modules.

I found one "solution" within this groups here, but didn't really work

set @a=""
select @a:=concat(@a,',',Name) from Table where ID=1

It's supposed to concat the Name with a "," to the variable @a, and
append it to @a more/less. When trying that in phpMyAdmin, I get a
Syntax error after the 2nd @a, namely at >',',Name)...< as if concat
only takes one argument.

MySQL 4.1
*no* root access, only user

Well, I hope theres a way to do that, as long as I can keep the number
of additional querys down to a minimum.

Thanks in advance,

Re: Concatenating multiple rows to one field? wrote:
Quoted text here. Click to load it

I don't know if the CONCAT() or CONCAT_WS() functions take datasets in
rows, but if they do you might want to consider the CONCAT_WS
(concatenate with separator) function, for the latter the syntax is like
this: the first parameter is the separator and the following values need
to be separated by the separator

As you did not post the error message it is guessing what went wrong...
buit you did not perhaps forget to replace the 'Table' part with your


Re: Concatenating multiple rows to one field?

Quoted text here. Click to load it

I think you can use MySQL's GROUP_CONCAT() function to achieve what you
This is a MySQL extension to SQL and it's very useful.

For example:

FROM myTable


Bill K.

Re: Concatenating multiple rows to one field?

Thanks for your answers so far, but...

Bill Karwin wrote:
Quoted text here. Click to load it tells me:
Quoted text here. Click to load it

Problem there is, That box runs MySQL 4.0.21, and I cant update it.
(Because I'm not root there)

As for CONCAT_WS(), this works same way as CONCAT(), which is not
working for me...
Neither select ID,concat(Name) from Table group by ID nor select ID,
concat(select Name from Table t1 where t1.ID=t2.ID) from Table t2
works. I think CONCAT() does not take sets as argument, at least it
doesn't in MySQL 4.0.21

I hope theres still a way to get this done without bothering the DB to
return all names for a specified ID, and then concat within PHP, get
next names for next ID and so on...

Thanks in Advance,

Site Timeline