texto - ¿Cómo creo una lista separada por comas usando una consulta SQL?

separar por comas en sql

Question

Tengo 3 tablas llamadas:

• Aplicaciones (id, nombre)
• Recursos (id, nombre)
• ApplicationsResources (id, app_id, resource_id)

Quiero mostrar en una GUI una tabla de todos los nombres de recursos. En una celda de cada fila me gustaría enumerar todas las aplicaciones (separadas por comas) de ese recurso.

Entonces, la pregunta es, ¿cuál es la mejor manera de hacer esto en SQL ya que necesito obtener todos los recursos y también necesito obtener todas las aplicaciones para cada recurso?

¿Ejecuto primero un * select de recursos y luego recorro cada recurso y realizo una consulta por recurso por separado para obtener la lista de aplicaciones para ese recurso?

¿Hay alguna manera de que pueda hacer esto en una consulta?

Answer 1

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

Oráculo

Recomiendo leer acerca de la agregación / concatenación de cadenas en Oracle .

Answer 2

A partir de la próxima versión de SQL Server , podrá hacer

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 

Para las versiones anteriores del producto hay una gran variedad de enfoques diferentes para este problema. Una excelente revisión de ellos está en el artículo: Concatenating Row Values ​​en Transact-SQL .

• Valores de concatenación cuando no se conoce el número de elementos

  • Método CTE recursivo
  • Los métodos de Blackbox XML
  • Uso del Common Language Runtime
  • UDF escalar con recursión
  • Tabla de UDF con un bucle WHILE
  • SQL dinámico
  • El enfoque del Cursor
    .

• Enfoques no confiables

  • UDF escalar con extensión de actualización t-SQL
  • UDF escalar con concatenación variable en SELECT

Answer 3

Asumiendo SQL Server:

Estructura de la tabla:

CREATE TABLE [dbo].[item_dept](
    [ItemName] char(20) NULL,
    [DepartmentID] int NULL   
)

Consulta:

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

Resultados:

ItemName    DepartmentID
item1       21,13,9,36
item2       4,9,44

Answer 4

Creo que lo que quieres es:

SELECT ItemName, GROUP_CONCAT(DepartmentId) FROM table_name GROUP BY ItemName

Si estás usando MySQL

Referencia

• http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Answer 5

Creo que podríamos escribir de la siguiente manera para recuperar (a continuación, el código es solo un ejemplo, modifíquelo según sea necesario):

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

Answer 6

Esto lo hará en SQL Server:

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

Answer 7

No hay forma de hacerlo de manera DB-agnóstica. Entonces necesitas obtener todo el conjunto de datos así:

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

Y luego concat el AppName programáticamente mientras se agrupa por ResName .

Answer 8

No sé si hay alguna solución para hacer esto de una manera independiente de la base de datos, ya que lo más probable es que necesite algún tipo de manipulación de cadenas, y esas suelen ser diferentes entre los proveedores.

Para SQL Server 2005 y versiones posteriores, puede usar:

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

Utiliza el constructo SQL Server 2005 FOR XML PATH para listar los subelementos (las aplicaciones para un recurso dado) como una lista separada por comas.

Bagazo

Answer 9

Para ser agnóstico, retrocede y 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;

Ahora usa el lenguaje de programación del servidor para concatenar a_names mientras que r_name es igual a la última vez.

Answer 10

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

**

Servidor MS SQL

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;

Oráculo

  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;

Answer 11

Uso de COALESCE para construir cadenas delimitadas por comas en SQL Server
http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

Ejemplo:

DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + '', '', '''') + 
   CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @EmployeeList