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?

---

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.

---

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('.','VARCHAR(max)'), 1, 1, '')
 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.

---

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

链接地址: http://www.djcxy.com/p/44416.html

上一篇: SQL Server中的concat函数

下一篇: 使用SQL查询分隔列表？