leora leora - 7 months ago 21
SQL Question

How do I Create a Comma-Separated List using a SQL Query?

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

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.