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

Question

I have 3 tables called:

• Applications (id, name)
• Resources (id, name)
• ApplicationsResources (id, app_id, resource_id)

I want to show on a GUI a table of all resource names. In one cell in each row I would like to list out all of the applications (comma separated) of that resource.

So the question is, what is the best way to do this in SQL as I need to get all resources and I also need to get all applications for each resource?

Do I run a select * from resources first and then loop through each resource and do a separate query per resource to get the list of applications for that resource?

Is there a way I can do this in one query?

Answer 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.

Answer 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

Answer 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

Answer 4

I believe what you want is:

SELECT ItemName, GROUP_CONCAT(DepartmentId) FROM table_name GROUP BY ItemName

If you're using MySQL

Reference

• http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Answer 5

Assuming SQL Server:

Table structure:

CREATE TABLE [dbo].[item_dept](
    [ItemName] char(20) NULL,
    [DepartmentID] int NULL   
)

Query:

SELECT ItemName,
       STUFF((SELECT ',' + rtrim(convert(char(10),DepartmentID))
        FROM   item_dept b
        WHERE  a.ItemName = b.ItemName
        FOR XML PATH('')),1,1,'') DepartmentID
FROM   item_dept a
GROUP BY ItemName

Results:

ItemName    DepartmentID
item1       21,13,9,36
item2       4,9,44

Answer 6

I think we could write in the following way to retrieve(below code is just an example, please modify as needed):

Create FUNCTION dbo.ufnGetEmployeeMultiple(@DepartmentID int)
RETURNS VARCHAR(1000) AS

BEGIN

DECLARE @Employeelist varchar(1000)

SELECT @Employeelist = COALESCE(@Employeelist + ', ', '') + E.LoginID
FROM humanresources.Employee E

Left JOIN humanresources.EmployeeDepartmentHistory H ON
E.BusinessEntityID = H.BusinessEntityID

INNER JOIN HumanResources.Department D ON
H.DepartmentID = D.DepartmentID

Where H.DepartmentID = @DepartmentID

Return @Employeelist

END

SELECT D.name as Department, dbo.ufnGetEmployeeMultiple (D.DepartmentID)as Employees
FROM HumanResources.Department D

SELECT Distinct (D.name) as Department, dbo.ufnGetEmployeeMultiple (D.DepartmentID) as 
Employees
FROM HumanResources.Department D