If you need to glue the output of a SELECT command, which is essentially a set of rows, into one string separated by a specified delimiter - here's the solution. MySQL uses the GROUP_CONCAT operator for this:
SELECT GROUP_CONCAT(expr SEPARATOR ',')
For example:
SELECT GROUP_CONCAT(title SEPARATOR ';') FROM mytable
instead of outputting several rows with title, it will output just one, joined by the ';' character. Here is the original table and the query result:
mytable
---
id title
1 one
2 two
3 three
result
---
one;two;three
Another example:
SELECT (SELECT GROUP_CONCAT(mytable2.value SEPARATOR ':') FROM mytable2 WHERE mytable2.reason=mytable1.sourcevalue) FROM mytable1;
In this example, we extract from table mytable2 the value of rows whose reason matches the sourcevalue of the first table, and combine it all into one string with the ':' delimiter (there are several strings because the concatenation happens for each row of mytable1). Here is an example of the tables and the result (for this query):
mytable1:
---
sourcevalue
1
2
3
mytable2:
---
reason value
1 A
1 B
2 C
3 D
4 E
result:
---
A:B
C
D
PS. ATTENTION! This function has a default limit of 1024 characters in the concatenated string. To change this limit, you need to run the following query (given the appropriate privileges):
SET group_concat_max_len = 16384;
where instead of 16384 you specify the maximum size of the concatenated string in characters.
Comments