How to Create Comma-Separated Lists of Associated Application Names in SQL?

Create comma separated list using SQL query

When multiple tables contain application and resource data, a common task is to list a table that lists all resource names along with the associated application names separated by commas. To achieve this using a single SQL query, follow these steps:

MySQL:

<code class="language-sql">SELECT r.name,
         GROUP_CONCAT(a.name SEPARATOR ',') AS application_names
    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(','), '') AS application_names
 FROM RESOURCES r</code>

SQL Server (2017):

<code class="language-sql">SELECT r.name,
         STRING_AGG(a.name, ',') AS application_names
    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 several string aggregation/joining options. Please refer to the documentation for specific syntax and requirements.

These queries join the "RESOURCES" table with the "APPLICATIONSRESOURCES" and "APPLICATIONS" tables to retrieve resource names and associated application names. The "GROUP_CONCAT", "STUFF" or "STRING_AGG" function is then used to concatenate the application name into a comma separated list of each resource. To improve readability, we added aliases application_names to the result columns.

The above is the detailed content of How to Create Comma-Separated Lists of Associated Application Names in SQL?. For more information, please follow other related articles on the PHP Chinese website!