How to Efficiently Generate Comma-Separated Lists from Multiple SQL Tables?

Integrate SQL queries to generate comma separated lists

In SQL, it is often necessary to generate comma-separated lists from data in multiple tables. For example, suppose you have three tables: Applications(id,name), Resources(id,name), and ApplicationsResources(id,app_id,resource_id), and you want to create a table that displays all resource names, where each row contains the A comma-separated list of applications associated with the resource.

The easiest way is to perform a separate query for each resource, but this is inefficient. Instead, we can leverage more complex SQL techniques to achieve this in a single query.

Method:

The following SQL query demonstrates how to achieve this:

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:

In Oracle, string aggregations and joins are handled differently. Please refer to Oracle documentation for specific string processing techniques.

The above is the detailed content of How to Efficiently Generate Comma-Separated Lists from Multiple SQL Tables?. For more information, please follow other related articles on the PHP Chinese website!