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