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

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_idGROUP 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_idGROUP BY r.name

Oracle

I recommend reading about string aggregation/concatentation in Oracle.

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 SalesCallsEmployeesWHERE SalCal_UniqueID = 1SELECT @EmployeeList

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