How Can I Retrieve MySQL Query Results as a Comma-Separated List?

Get MySQL query results as a comma separated list

In MySQL, you may need to retrieve the query results as a comma separated list. Although you can use subqueries to extract specific information, you will encounter some limitations when it comes to presenting the data in the desired format.

Question:

Consider the following query:

<code class="language-sql">SELECT p.id, p.name, 
       (SELECT name 
          FROM sites s 
         WHERE s.id = p.site_id) AS site_list
  FROM publications p</code>

The subquery returns each site name as a separate column, which does not match the desired comma separated list result.

Solution:

MySQL provides the GROUP_CONCAT function to achieve this goal. By leveraging this function, you can modify your query as follows:

<code class="language-sql">SELECT p.id, p.name, GROUP_CONCAT(s.name) AS site_list
FROM sites s
INNER JOIN publications p ON(s.id = p.site_id)
GROUP BY p.id, p.name;</code>

GROUP_CONCAT Concatenates the values ​​in the specified column (in this case, the "name" column in the "sites" table) and separates them with a predefined delimiter (usually a comma). The results are then grouped by the "id" and "name" columns in the "publications" table, providing a unique record for each publication and its associated sites, which are displayed as a comma-separated list.

The above is the detailed content of How Can I Retrieve MySQL Query Results as a Comma-Separated List?. For more information, please follow other related articles on the PHP Chinese website!