How Can I Generate Comma-Separated Lists Using SQL Queries?

Use SQL query to generate comma separated list

During database operations, it is often necessary to generate a comma-separated list of values. For example, you might want to display a list of all applications associated with a specific resource in the user interface. While it is possible to loop through each resource, retrieve the application, and connect them yourself, this approach is inefficient and time-consuming.

Fortunately, most SQL databases provide built-in string concatenation and aggregate functions. The following are the implementation methods of several commonly used SQL dialects:

MySQL

<code class="language-sql">SELECT r.name,
       GROUP_CONCAT(a.name SEPARATOR ',')
FROM RESOURCES r
JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id
JOIN APPLICATIONS a ON a.id = ar.app_id
GROUP BY r.name</code>

SQL Server (2005)

<code class="language-sql">SELECT r.name,
       STUFF((SELECT ',' + a.name
               FROM APPLICATIONS a
               JOIN APPLICATIONRESOURCES ar ON ar.app_id = a.id
              WHERE ar.resource_id = r.id
           GROUP BY a.name
            FOR XML PATH(''), TYPE).value('text()[1]','NVARCHAR(max)'), 1, LEN(','), '')
 FROM RESOURCES r</code>

SQL Server (2017)

<code class="language-sql">SELECT r.name,
       STRING_AGG(a.name, ',')
FROM RESOURCES r
JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id
JOIN APPLICATIONS a ON a.id = ar.app_id
GROUP BY r.name</code>

Oracle

Oracle provides a variety of string concatenation and aggregation options, please familiarize yourself with the specific syntax of the version you are using.

These queries effectively generate a comma-separated list of applications for each resource, allowing you to provide the necessary information to your GUI with a single query.

The above is the detailed content of How Can I Generate Comma-Separated Lists Using SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!