How to Concatenate Strings from Multiple Rows in MySQL using GROUP BY?

Use MySQL’s GROUP BY to connect multi-line strings

A common task in database operations is concatenating multi-line strings. In MySQL, the GROUP BY clause provides a convenient way to accomplish this.

The task is to convert the table as shown below:

foo_id	foo_name
1	A
1	B
2	C

Convert to:

foo_id	foo_name
1	A B
2	C

The solution involves using the GROUP_CONCAT function and the GROUP BY clause:

<code class="language-sql">SELECT foo_id, GROUP_CONCAT(foo_name SEPARATOR ' ') FROM table GROUP BY foo_id;</code>

Let’s break down the statement:

• GROUP_CONCAT: This function concatenates the non-NULL values ​​of each group in the specified column (foo_name in this case).
• GROUP BY foo_id: This clause groups the rows by the foo_id column, ensuring that the join operation is performed on each unique foo_id value.
• SEPARATOR ' ' (optional): This specifies the character used to separate concatenated values ​​(' ' adds a space between names).

In our example, this function will concatenate the foo_name values ​​for each foo_id to get the desired output.

For more information about GROUP_CONCAT, see the MySQL documentation: https://www.php.cn/link/18fc3b6cc1e55ccea877c161e2e9ba27.

The above is the detailed content of How to Concatenate Strings from Multiple Rows in MySQL using GROUP BY?. For more information, please follow other related articles on the PHP Chinese website!