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?

@prabhats.net: You may be new to SO, so remember to upvote helpful answers and accept the one that was most helpful to you. — Bill Karwin
– Bill Karwin, Commented Jun 21, 2010 at 20:02

Charlieface · Accepted Answer · 2021-06-30 15:02:29Z

139

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.

edited Jun 30, 2021 at 15:02

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

answered Nov 30, 2009 at 5:55

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Uncle Iroh Over a year ago

Also worth noting is that group_concat defaults to ',' separated so group_concat(a.name) is equivalent to group_concat(a.name SEPARATOR ',')

danielson317 Over a year ago

SQLite is the same as MySQL except the function looks like: GROUP_CONCAT(a.name, ', ')

Charlieface · Accepted Answer · 2021-06-30 15:07:24Z

35

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

edited Jun 30, 2021 at 15:07

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

answered Nov 30, 2009 at 5:38

Robert Harvey

182k48 gold badges349 silver badges516 bronze badges

3 Comments

FistOfFury Over a year ago

this seems a much better solution than the higher rated answers. Easy and versatile.

Gary Over a year ago

What if you want to EmployeeList per (Group by) department?

Robert Harvey Over a year ago

@Gary: Then add a GROUP BY clause.

marc_s · Accepted Answer · 2017-04-10 11:41:07Z

11

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

edited Apr 10, 2017 at 11:41

answered Nov 30, 2009 at 5:51

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

2 Comments

Magnus Smith Over a year ago

I believe you are missing an "r". The INNER JOIN should say "ON ar.app_id = a.id". Works for me now. Thanks.

marc_s Over a year ago

@MagnusSmith: absolutely! Thanks for your "eagle eyes" spotting this typo! Fixed.

Jamie Wong · Accepted Answer · 2010-06-21 19:22:03Z

5

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

answered Jun 21, 2010 at 19:22

Jamie Wong

18.4k8 gold badges66 silver badges81 bronze badges

4 Comments

Bill Karwin Over a year ago

GROUP_CONCAT only works in MySQL and SQLite. The OP said in a comment that they're using MS SQL Server 2008.

Jamie Wong Over a year ago

Didn't see the comment until after the post. Should I delete my answer for irrelevance or just leave it for interest?

Wick Over a year ago

...years later, but for the record... I'd leave it, it's helpful & the OP's title is ambiguous. I was searching for a solution for MySQL, this question came up (it's tagged as mysql) & this answer helped me!

Praesagus Over a year ago

Your answer comes up in the serps for mysql. Saved me hours - thanks for leaving it in.

Kenneth · Accepted Answer · 2010-06-21 19:25:22Z

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

answered Jun 21, 2010 at 19:25

Kenneth

1,3641 gold badge8 silver badges12 bronze badges

2 Comments

Bill Karwin Over a year ago

+1 This works, but IMHO this solution for SQL Server is a hack, tremendously ugly and non-intuitive. Not your fault though. :)

Martin Smith Over a year ago

There is another SQL Server solution possible with custom CLR aggregates if guaranteed Order of the delimited items is not essential.

user1696097 · Accepted Answer · 2012-09-25 03:31:31Z

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

Martin Smith · Accepted Answer · 2016-11-19 11:10:39Z

From next version of SQL Server you will be able to do

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

For previous versions of the product there are quite a wide variety of different approaches to this problem. An excellent review of them is in the article: Concatenating Row Values in Transact-SQL.

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

Rick Smith · Accepted Answer · 2016-10-04 19:26:04Z

2

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

And then concat the AppName programmatically while grouping by ResName.

edited Oct 4, 2016 at 19:26

Rick Smith

9,31916 gold badges85 silver badges86 bronze badges

answered Nov 30, 2009 at 6:03

Dmytrii Nagirniak

24.2k13 gold badges80 silver badges132 bronze badges

Comments

John G Hohengarten · Accepted Answer · 2016-06-27 19:33:52Z

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

**

MS 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(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
 FROM RESOURCES r
 GROUP BY deptno;

Oracle

  SELECT r.name,
         LISTAGG(a.name SEPARATOR ',') WITHIN GROUP (ORDER BY 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;

Don · Accepted Answer · 2009-11-30 05:56:40Z

1

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.

answered Nov 30, 2009 at 5:56

Don

4,6832 gold badges29 silver badges32 bronze badges

Comments

Abe Miessler · Accepted Answer · 2010-06-21 19:20:12Z

1

This will do it in SQL Server:

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

answered Jun 21, 2010 at 19:20

Abe Miessler

85.7k104 gold badges323 silver badges496 bronze badges

1 Comment

Martin Smith Over a year ago

It won't do it for each GROUP BY though. For that you would need to adjust marc_s's answer in the possible duplicate question stackoverflow.com/questions/1817985/… (or see Kenneth's answer in this question)

Collectives™ on Stack Overflow

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

11 Answers 11

MySQL

SQL Server (2005+)

SQL Server (2017+)

Oracle

2 Comments

NOTE:

3 Comments

2 Comments

4 Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

MySQL

SQL Server (2005+)

SQL Server (2017+)

Oracle

2 Comments

NOTE:

3 Comments

2 Comments

4 Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Linked

Related