How do I create a comma-separated list using a SQL query?

Solution 1:

MySQL

  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

SQL Server (2005+)

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

SQL Server (2017+)

  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

Oracle

I recommend reading about string aggregation/concatentation in Oracle.

Solution 2:

NOTE:

This method is not recommended as it can give incorrect or non-deterministic results.
This has been documented on StackOverflow and DBA

Using COALESCE to Build Comma-Delimited String in SQL Server
http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

Example:

DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 
   CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @EmployeeList

Solution 3:

I don't know if there's any solution to do this in a database-agnostic way, since you most likely will need some form of string manipulation, and those are typically different between vendors.

For SQL Server 2005 and up, you could use:

SELECT
     r.ID, r.Name,
     Resources = STUFF(
       (SELECT ','+a.Name
        FROM dbo.Applications a
        INNER JOIN dbo.ApplicationsResources ar ON ar.app_id = a.id
        WHERE ar.resource_id = r.id
        FOR XML PATH('')), 1, 1, '')
FROM
     dbo.Resources r

It uses the SQL Server 2005 FOR XML PATH construct to list the subitems (the applications for a given resource) as a comma-separated list.

Marc