Cómo concatenar texto de varias filas en una sola cadena de texto en SQL Server
Considere una tabla de base de datos que contiene nombres, con tres filas:
Peter
Paul
Mary
¿Existe una manera fácil de convertir esto en una sola cadena de Peter, Paul, Mary?
Si tiene SQL Server 2017 o Azure, consulte la respuesta de Mathieu Renda .
Tuve un problema similar cuando intentaba unir dos tablas con relaciones de uno a muchos. En SQL 2005 descubrí que ese XML PATHmétodo puede manejar la concatenación de filas muy fácilmente.
Si hay una tabla llamadaSTUDENTS
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é lo 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 (''), TYPE
).value('text()[1]','nvarchar(max)') [Students]
FROM dbo.Students ST2
) [Main]
Puede hacer lo mismo de una manera más compacta si puede combinar las comas al principio y usarlas substringpara omitir la primera, de modo que no necesite realizar 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 (''), TYPE
).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2
Feb 13 '2009 11:02
Esta respuesta puede arrojar resultados inesperados. Para obtener resultados consistentes, use uno de los métodos FOR XML PATH detallados en otras respuestas.
Usar COALESCE:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
Solo una explicación (ya que esta respuesta parece recibir visitas relativamente regulares):
- Coalesce es en realidad sólo un truco útil que logra dos cosas:
1) No es necesario inicializar @Namescon 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 valor de Nombre NULL (si hay un NULL , NULL se convertirá en
@NamesNULL después de esa fila y la siguiente fila comenzará nuevamente como una cadena vacía. Se soluciona fácilmente con uno 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 simplemente filtra los mensajes NULL , la segunda opción los mantiene en la lista con un mensaje de marcador [reemplace 'N/A' con lo que sea apropiado para usted]).
