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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

This will do it in SQL Server   DECLARE  listStr VARCHAR MAX  SELECT  listStr   COALESCE  listStr            Convert nvarchar 8  DepartmentId  FROM Table SELECT  listStr

User · Answer

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

User · Answer

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

User · Answer

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

User · 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   And then concat the AppName programmatically while grouping by ResName

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to string-aggregation