Pregunta ¿Cómo concatenar texto de múltiples filas en una sola cadena de texto en el servidor SQL?


Considere una tabla de base de datos con nombres, con tres filas:

Peter
Paul
Mary

¿Hay alguna manera fácil de convertir esto en una sola cadena de Peter, Paul, Mary?


1483
2017-10-11 23:49


origen


Respuestas:


Si está en SQL Server 2017 o Azure, consulte Respuesta de Mathieu Renda.

Tuve un problema similar cuando intentaba unir dos tablas con relaciones uno a muchos. En SQL 2005 encontré que XML PATH método puede manejar la concatenación de las filas muy fácilmente.

Si hay una tabla llamada STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

El resultado que esperaba era:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

Usé el siguiente T-SQL:

Select Main.SubjectID,
       Left(Main.Students,Len(Main.Students)-1) As "Students"
From
    (
        Select distinct ST2.SubjectID, 
            (
                Select ST1.StudentName + ',' AS [text()]
                From dbo.Students ST1
                Where ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                For XML PATH ('')
            ) [Students]
        From dbo.Students ST2
    ) [Main]

Puede hacer lo mismo de una manera más compacta si puede concomitar las comas al principio y usarlas substring omitir el primero para no tener que hacer una subconsulta:

Select distinct ST2.SubjectID, 
    substring(
        (
            Select ','+ST1.StudentName  AS [text()]
            From dbo.Students ST1
            Where ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            For XML PATH ('')
        ), 2, 1000) [Students]
From dbo.Students ST2

1105
2018-02-13 11:53



Esta respuesta puede devolver resultados inesperados cuando una cláusula ORDER BY está presente. Para resultados consistentes, use uno de los métodos FOR XML PATH detallados en otras respuestas.

Utilizar COALESCE:

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name 
FROM People

Solo una explicación (ya que esta respuesta parece tener vistas relativamente regulares):

  • Coalesce es realmente una trampa útil que logra dos cosas:

1) No es necesario inicializar @Names con un valor de cadena vacío.

2) No es necesario quitar un separador adicional al final.

  • La solución anterior dará resultados incorrectos si una fila tiene un NULO Nombre de valor (si hay un NULO, el NULO hará @Names  NULO después de esa fila, y la siguiente fila comenzará nuevamente como una cadena vacía. Se soluciona fácilmente con una de dos soluciones:
DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL

o:

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + 
    ISNULL(Name, 'N/A')
FROM People

Dependiendo del comportamiento que desee (la primera opción solo filtra NULOs out, la segunda opción los mantiene en la lista con un mensaje marcador [reemplace 'N / A' con lo que sea apropiado para usted]).


893
2017-10-12 00:18



Un método aún no mostrado a través del XML  data() comando en MS SQL Server es:

Asumir una tabla llamada NameList con una columna llamada FName,

SELECT FName + ', ' AS 'data()' 
FROM NameList 
FOR XML PATH('')

devoluciones:

"Peter, Paul, Mary, "

Solo se debe tratar la coma adicional.

Editar: Según se adoptó del comentario de @NReilingh, puede usar el siguiente método para eliminar la coma final. Suponiendo los mismos nombres de tabla y columna:

STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(FName)) AS 'data()' FROM NameList
FOR XML PATH('')),' #!',', '), 1, 2, '') as Brands

296
2018-04-05 21:19



En SQL Server 2005

SELECT Stuff(
  (SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')

En SQL Server 2016

puedes usar el PARA sintaxis JSON

es decir

SELECT per.ID,
Emails = JSON_VALUE(
   REPLACE(
     (SELECT _ = em.Email FROM Email em WHERE em.Person = per.ID FOR JSON PATH)
    ,'"},{"_":"',', '),'$[0]._'
) 
FROM Person per

Y el resultado será

Id  Emails
1   abc@gmail.com
2   NULL
3   def@gmail.com, xyz@gmail.com

Esto funcionará incluso si sus datos contienen caracteres XML no válidos

el '"},{"_":"' es seguro porque si sus datos contienen '"},{"_":"', se escapará a "},{\"_\":\"

Puedes reemplazar ', ' con cualquier separador de cuerdas


Y en SQL Server 2017, Azure SQL Database

Puedes usar el nuevo Función STRING_AGG


214
2018-03-14 05:00



SQL Server 2017+ y SQL Azure: STRING_AGG

Comenzando con la próxima versión de SQL Server, finalmente podemos concatenar entre filas sin tener que recurrir a ninguna brujería variable o XML.

STRING_AGG (Transact-SQL)

Sin agrupar

SELECT STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department;

Con la agrupación:

SELECT GroupName, STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department
GROUP BY GroupName;

Con agrupamiento y subdivisión

SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
FROM HumanResources.Department 
GROUP BY GroupName;

167
2017-10-12 00:10



En MySQL hay una función, GROUP_CONCAT (), que le permite concatenar los valores de múltiples filas. Ejemplo:

SELECT 1 AS a, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS people 
FROM users 
WHERE id IN (1,2,3) 
GROUP BY a

98
2018-04-05 07:08



Utilizar JUNTARSE - Aprende más de aquí

Para un ejemplo:

102

103

104

A continuación, escriba el código siguiente en el servidor sql,

Declare @Numbers AS Nvarchar(MAX) -- It must not be MAX if you have few numbers 
SELECT  @Numbers = COALESCE(@Numbers + ',', '') + Number
FROM   TableName where Number IS NOT NULL

SELECT @Numbers

La salida sería:

102,103,104

52
2018-03-08 16:29



Oracle 11g Release 2 es compatible con la función LISTAGG. Documentación aquí.

COLUMN employees FORMAT A50

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

Advertencia

Tenga cuidado al implementar esta función si existe la posibilidad de que la cadena resultante supere los 4000 caracteres. Lanzará una excepción. Si ese es el caso, entonces necesita manejar la excepción o implementar su propia función que evite que la cadena unida supere los 4000 caracteres.


42
2017-08-09 21:20



Las matrices de Postgres son increíbles. Ejemplo:

Crea algunos datos de prueba:

postgres=# \c test
You are now connected to database "test" as user "hgimenez".
test=# create table names (name text);
CREATE TABLE                                      
test=# insert into names (name) values ('Peter'), ('Paul'), ('Mary');                                                          
INSERT 0 3
test=# select * from names;
 name  
-------
 Peter
 Paul
 Mary
(3 rows)

Agréguelos en una matriz:

test=# select array_agg(name) from names;
 array_agg     
------------------- 
 {Peter,Paul,Mary}
(1 row)

Convierta la matriz en una cadena delimitada por comas:

test=# select array_to_string(array_agg(name), ', ') from names;
 array_to_string
-------------------
 Peter, Paul, Mary
(1 row)

HECHO

Desde PostgreSQL 9.0 es aún más fácil.


41
2017-07-06 12:46



En SQL Server 2005 y posterior, use la consulta siguiente para concatenar las filas.

DECLARE @t table
(
    Id int,
    Name varchar(10)
)
INSERT INTO @t
SELECT 1,'a' UNION ALL
SELECT 1,'b' UNION ALL
SELECT 2,'c' UNION ALL
SELECT 2,'d' 

SELECT ID,
stuff(
(
    SELECT ','+ [Name] FROM @t WHERE Id = t.Id FOR XML PATH('')
),1,1,'') 
FROM (SELECT DISTINCT ID FROM @t ) t

27
2017-10-12 00:16



No tengo acceso a un servidor SQL en casa, así que supongo que aquí está la sintaxis, pero es más o menos:

DECLARE @names VARCHAR(500)

SELECT @names = @names + ' ' + Name
FROM Names

23
2017-08-09 21:06