Concatenate results into a single field

[saturaze]

Say I have the two tables listed below in my database.

Table #1:  persons
+----------------+
| id | name      |
+----+-----------+
| 23 | Oluf      |
| 24 | Christian |
| 25 | Jenny     |
+----+-----------+


Table #2:  person_pets
+----------------------+
| person_id | name     |
+-----------+----------+
| 23        | Fido     |
| 23        | Garfield |
| 23        | Casper   |
| 25        | Shadow   |
+-----------+----------+

Now, how can I construct a query which yields the results like this,
where the persons' pets are listed in a single field, separated with
a comma (or any other chosen character). Is it possible?

+---------------------------------------+
| id | name      | pet_names            |
+---------------------------------------+
| 23 | Oluf      | Fido,Garfield,Casper |
| 24 | Christian |                      |
| 25 | Jenny     | Shadow               |
+---------------------------------------+

[dc dalton]

You could try CONCAT_WS(). I'd be kind of curious why you want to do this.

[r937]

sorry, CONCAT_WS in not right

you want to use GROUP_CONCAT instead

[beingzoe]

I'd be kind of curious why you want to do this.

To make a database VIEW containing potentially ephemeral and complex information that would otherwise be a pain or a waste of CPU to do programmatically from the resulting dataset.

In my case I have several tables joined together into a VIEW. Data from each table can be viewed separately without the VIEW as unque entity.

The VIEW itself represents a more specific meta concept when the various tables are combined.

I store a 'slug' (url safe string for friendly urls) with each record in each table. In order to make a 'slug' or 'path' from the data in the view means combining all the strings and ids from each table that is in the view. I was just doing that in my server side code but when I started doing some complex grouping output of data looping through arrays, all the necessary data was not readily available.

Then it hit me, that I could just let MySQL give me that information from the start since it has all the information ready to go.

So I added a new virtual 'slug' to my view and now I don't have to manually concatenate that information from the result set.

I know I revived an older topic, but this post came up when I was searching for MySQL CONCAT & CONCAT_WS functions. And in my most recent project I have found so much help and gotten so many ideas from generous folks in forums I thought I would share why I'm doing it to hopefully inspire others to find solutions.

merry day.

[oddz]

You may end up with truncated data when using GROUP_CONCAT() due to the 1024 default character limit. That is why many times it is best to handle this type of logic within the application language unless your certain the returned data will not go beyond 1024 characters.

MySQL Manual

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024.

Given very sensitive data using GROUP_CONCAT() could result in a disaster if you don't consider that it will truncate data that goes beyond the default limit unless you increase it manually.

http://dev.mysql.com/doc/refman/5.0/...functions.html

[beingzoe]

Thanks for the heads up oddz.

Though I am not currently using GROUP_CONCAT (group_by function), only CONCAT and CONCAT_WS (string functions).

It does explicitly say in the MySQL documentation that there is a limit to the results when using the string concatenation functions but I will bear that in mind if it comes up. In my case I am combining 4 strings (100 characters max each) and an id (which I could only hope would become long enough to be a problem

Hopefully someone finds this information useful or inspiring. I've only recently started to exploit the deeper powers and potential of SQL functions and it is changing my life