Concatenating multiple rows to one field?

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.

Example:

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

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

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,
BhaaL

Re: Concatenating multiple rows to one field?

bhaalsen@gmail.com wrote:
> 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.
>
> Example:
>
> 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
>
> *code*
> set @a=""
> select @a:=concat(@a,',',Name) from Table where ID=1
> */code*
>

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
(http://dev.mysql.com/doc/refman/5.0/en/string-functions.htm l)

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
tablename?

Jonathan

Re: Concatenating multiple rows to one field?

wrote in message
news:1138212177.920645.19150@g43g2000cwa.googlegroups.com...
> I got a slight problem with a web application, its supposed to several
> entries for a certain ID, and display them as one string.

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

For example:

SELECT ID, GROUP_CONCAT(Name)
FROM myTable
GROUP BY ID

See http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.ht ml

Regards,
Bill K.

Re: Concatenating multiple rows to one field?

Thanks for your answers so far, but...

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

mysql.com tells me:
> GROUP_CONCAT() was added in MySQL 4.1.

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,
BhaaL