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

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

Answer 2

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 a.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 3

MySQL

  SELECT r.name,
         GROUP_CONCAT(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

SQL Server

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('')), 1, 1, '')
 FROM RESOURCES r

Oracle

I recommend reading about string aggregation/concatentation in Oracle.

Answer 4

To be agnostic, drop back and punt.

Select a.name as a_name, r.name as r_name
  from ApplicationsResource ar, Applications a, Resources r
 where a.id = ar.app_id
   and r.id = ar.resource_id
 order by r.name, a.name;

Now user your server programming language to concatenate a_names while r_name is the same as the last time.

Answer 5

There is no way to do it in a DB-agnostic way. So you need to get the whole data-set like this:

select 
  r.name as ResName, 
  a.name as AppName
from 
  Resouces as r, 
  Applications as a, 
  ApplicationsResources as ar
where
  ar.app_id = a.id 
  and ar.resource_id = r.id

Adn then concat the AppName programmatically while grouping by ResName.

Answer 6

If you're using oracle, I've used the stringagg aggregate function described at asktom http://asktom.oracle.com/pls/asktom/f?p=100:11:4217574372575887::::P11_QUESTION_ID:229614022562

In sqlserver 2005, i just wrote an aggregate c# function to do it.

Answer 7

http://www.projectdmx.com/tsql/rowconcatenate.aspx

Answer 8

This will do it in SQL Server:

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Convert(nvarchar(8),DepartmentId)
FROM Table
SELECT @listStr

Answer 9

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 10

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 11

For Microsoft SQL Server there are quite a wide variety of different approaches to this problem. I've just found this excellent review of them here http://www.projectdmx.com/tsql/rowconcatenate.aspx

• Concatenating values when the number of items are not known

  • Recursive CTE method
  • The blackbox XML methods
  • Using Common Language Runtime
  • Scalar UDF with recursion
  • Table valued UDF with a WHILE loop
  • Dynamic SQL
  • The Cursor approach
    .

• Non-reliable approaches

  • Scalar UDF with t-SQL update extension
  • Scalar UDF with variable concatenation in SELECT