How to Convert MySQL Values into a Comma-Separated String?

Converting Selected MySQL Values into a Comma-Separated String

When working with MySQL, it is often necessary to retrieve data and convert it into a more manageable format. One common requirement is to transform a list of selected values into a comma-separated string.

Consider the following scenario: You have a table named table_level with a column called id and a parent_id column. You want to select all the id values where parent_id is equal to 4 and convert them into a single comma-separated string.

Problem Statement:

Your initial code is as follows:

SELECT id
FROM table_level
WHERE parent_id = 4;

This query will correctly return the id values, but it will display them as individual rows, making it difficult to work with them as a single string.

Desired Output:

The desired output should be in the following comma-separated string format:

"5,6,9,10,12,14,15,17,18,779"

Solution:

To achieve the desired output, you can utilize MySQL's GROUP_CONCAT() function. This function concatenates values from a specified column, separated by a specified delimiter. In this case, the delimiter will be a comma.

Modified Query:

SELECT GROUP_CONCAT(id)
FROM table_level
WHERE parent_id = 4
GROUP BY parent_id;

This modified query uses GROUP_CONCAT() to concatenate the id values, separated by commas, into a single string. The GROUP BY clause ensures that the concatenation is performed only for the specified parent_id value (4 in this case).

Output:

Executing the modified query will produce the desired comma-separated string output:

"5,6,9,10,12,14,15,17,18,779"

The above is the detailed content of How to Convert MySQL Values into a Comma-Separated String?. For more information, please follow other related articles on the PHP Chinese website!