uso - unir varias filas en una sola sql

¿Cómo concatenar texto de varias filas en una sola cadena de texto en el servidor SQL?

Question

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

Peter
Paul
Mary

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

Answer 1

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

Use 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 un truco ú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 valor de Nombre NULO (si hay un NULO , el valor NULL hará que @Names NULL después de esa fila, y la siguiente fila comenzará nuevamente como una cadena vacía de nuevo. Se arregla 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 NULL s out, la segunda opción los mantiene en la lista con un mensaje de marcador [reemplace ''N / A'' con lo que sea apropiado para usted]).

Answer 2

SQL Server 2017+ y SQL Azure: STRING_AGG

A partir de la próxima versión de SQL Server, finalmente podemos concatenar a través de filas sin tener que recurrir a ninguna variable o brujería 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 agrupación y subdivisión.

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

Answer 3

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 la sintaxis de FOR 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 [email protected]
2 NULL
3 [email protected], [email protected]

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

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

Puede reemplazar '', '' con cualquier separador de cadena

Y en SQL Server 2017, Azure SQL Database

Puedes usar la nueva función STRING_AGG

Answer 4

Comenzando con PostgreSQL 9.0 esto es bastante simple:

select string_agg(name, '','')
from names;

En versiones anteriores a 9.0, array_agg() se puede usar como se muestra en hgmnz

Answer 5

Con las otras respuestas, la persona que lee la respuesta debe conocer una tabla de dominio específica, como un vehículo o un estudiante. La tabla debe crearse y completarse con datos para probar una solución.

A continuación se muestra un ejemplo que utiliza la tabla "Information_Schema.Columns" de SQL Server. Al utilizar esta solución, no es necesario crear tablas ni agregar datos. Este ejemplo crea una lista separada por comas de nombres de columna para todas las tablas en la base de datos.

SELECT
Table_Name
,STUFF((
SELECT '','' + Column_Name
FROM INFORMATION_SCHEMA.Columns Columns
WHERE Tables.Table_Name = Columns.Table_Name
ORDER BY Column_Name
FOR XML PATH ('''')), 1, 1, ''''
)Columns
FROM INFORMATION_SCHEMA.Columns Tables
GROUP BY TABLE_NAME

Answer 6

El uso de XML me ayudó a obtener filas separadas con comas. Para la coma adicional podemos usar la función de reemplazo de SQL Server. En lugar de agregar una coma, el uso de AS ''data ()'' concatenará las filas con espacios, que luego se pueden reemplazar con comas como la sintaxis escrita a continuación.

REPLACE(
(select FName AS ''data()'' from NameList for xml path(''''))
, '' '', '', '')

Answer 7

En MySQL hay una función, Group_Concat , que le permite concatenar los valores de varias 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

Answer 8

En Oracle, es wm_concat . Creo que esta función está disponible en la versión 10g y superior.

Answer 9

En SQL Server 2005 y versiones posteriores, use la consulta a continuación 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

Answer 10

En SQL Server vNext, esto se integrará con la función STRING_AGG. Lea más sobre esto aquí: https://msdn.microsoft.com/en-us/library/mt790580.aspx

Answer 11

Esta respuesta requerirá algún privilegio en el servidor para funcionar.

Assemblies son una buena opción para usted. Hay muchos sitios que explican cómo crearlo. El que creo que está muy bien explicado es este.

Si lo desea, ya he creado el ensamblaje y es posible descargar la DLL here .

Una vez que lo haya descargado, deberá ejecutar la siguiente secuencia de comandos en su SQL Server:

CREATE Assembly concat_assembly
AUTHORIZATION dbo
FROM ''<PATH TO Concat.dll IN SERVER>''
WITH PERMISSION_SET = SAFE;
GO
CREATE AGGREGATE dbo.concat (
@Value NVARCHAR(MAX)
, @Delimiter NVARCHAR(4000)
) RETURNS NVARCHAR(MAX)
EXTERNAL Name concat_assembly.[Concat.Concat];
GO
sp_configure ''clr enabled'', 1;
RECONFIGURE

Observe que la ruta al ensamblaje puede ser accesible al servidor. Ya que ha realizado con éxito todos los pasos, puede usar la función como:

SELECT dbo.Concat(field1, '','')
FROM Table1

¡¡¡Espero eso ayude!!!

Answer 12

Este método se aplica a la base de datos Teradata Aster solo cuando utiliza su función NPATH.

Nuevamente, tenemos mesa de estudiantes.

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

Entonces con NPATH es solo SELECT:

SELECT * FROM npath(
ON Students
PARTITION BY SubjectID
ORDER BY StudentName
MODE(nonoverlapping)
PATTERN(''A*'')
SYMBOLS(
''true'' as A
)
RESULT(
FIRST(SubjectID of A) as SubjectID,
ACCUMULATE(StudentName of A) as StudentName
)
);

Resultado:

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

Answer 13

Esto puede ser útil también

create table #test (id int,name varchar(10))
--use separate inserts on older versions of SQL Server
insert into #test values (1,''Peter''), (1,''Paul''), (1,''Mary''), (2,''Alex''), (3,''Jack'')
DECLARE @t VARCHAR(255)
SELECT @t = ISNULL(@t + '','' + name, name) FROM #test WHERE id = 1
select @t
drop table #test

devoluciones

Peter,Paul,Mary

Answer 14

Los arreglos de Postgres son impresionantes. Ejemplo:

Crear 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)

Agregalos en una matriz:

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

Convierta la matriz a 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 .

Answer 15

Necesitas crear una variable que mantenga tu resultado final y seleccionarlo, así.

La solución más fácil

DECLARE @char VARCHAR(MAX);
SELECT @char = COALESCE(@char + '', '' + [column], [column])
FROM [table];
PRINT @char;

Answer 16

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

DECLARE @names VARCHAR(500)
SELECT @names = @names + '' '' + Name
FROM Names

Answer 17

Oracle 11g Release 2 admite la función LISTAGG. Documentación here .

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 tenga más de 4000 caracteres. Lanzará una excepción. Si ese es el caso, entonces necesita manejar la excepción o rodar su propia función que evite que la cadena unida supere los 4000 caracteres.

Answer 18

Para Oracle DBs, vea esta pregunta: ¿Cómo se pueden concatenar varias filas en una en Oracle sin crear un procedimiento almacenado?

La mejor respuesta parece ser la de @Emmanuel, utilizando la función LISTAGG () incorporada, disponible en Oracle 11g versión 2 y posterior.

SELECT question_id,
LISTAGG(element_id, '','') WITHIN GROUP (ORDER BY element_id)
FROM YOUR_TABLE;
GROUP BY question_id

como @ user762952 señaló, y de acuerdo con la documentación de Oracle http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php , la función WM_CONCAT () también es una opción. Parece estable, pero Oracle recomienda explícitamente no usarlo para ninguna aplicación SQL, así que úselo bajo su propio riesgo.

Aparte de eso, tendrás que escribir tu propia función; El documento de Oracle anterior tiene una guía sobre cómo hacerlo.

Answer 19

Para evitar valores nulos puedes usar CONCAT ()

DECLARE @names VARCHAR(500)
SELECT @names = CONCAT(@names, '' '', name)
FROM Names
select @names

Answer 20

Por lo general, uso este tipo de selección para concatenar cadenas en SQL Server:

with lines as
(
select
row_number() over(order by id) id, -- id is a line id
line -- line of text.
from
source -- line source
),
result_lines as
(
select
id,
cast(line as nvarchar(max)) line
from
lines
where
id = 1
union all
select
l.id,
cast(r.line + N'', '' + l.line as nvarchar(max))
from
lines l
inner join
result_lines r
on
l.id = r.id + 1
)
select top 1
line
from
result_lines
order by
id desc

Answer 21

Realmente me gustó la elegancia de la respuesta de Dana . Solo quería hacerlo completo.

DECLARE @names VARCHAR(MAX)
SET @names = ''''
SELECT @names = @names + '', '' + Name FROM Names
-- Deleting last two symbols ('', '')
SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)

Answer 22

Se sugirió una solución de CTE recursiva, pero no se proporcionó ningún código. El código a continuación es un ejemplo de un CTE recursivo: tenga en cuenta que aunque los resultados coinciden con la pregunta, los datos no coinciden con la descripción dada, ya que asumo que realmente desea hacer esto en grupos de filas, no todos filas en la tabla Cambiarlo para que coincida con todas las filas de la tabla se deja como un ejercicio para el lector.

;with basetable as
( SELECT id, CAST(name as varchar(max))name,
ROW_NUMBER() OVER(Partition By id order by seq) rw,
COUNT(*) OVER (Partition By id) recs
FROM (VALUES (1, ''Johnny'', 1), (1,''M'', 2),
(2,''Bill'', 1), (2, ''S.'', 4), (2, ''Preston'', 5), (2, ''Esq.'', 6),
(3, ''Ted'', 1), (3,''Theodore'', 2), (3,''Logan'', 3),
(4, ''Peter'', 1), (4,''Paul'', 2), (4,''Mary'', 3)
)g(id, name, seq)
),
rCTE as (
SELECT recs, id, name, rw from basetable where rw=1
UNION ALL
SELECT b.recs, r.ID, r.name +'', ''+ b.name name, r.rw+1
FROM basetable b
inner join rCTE r
on b.id = r.id and b.rw = r.rw+1
)
SELECT name FROM rCTE
WHERE recs = rw and ID=4

Answer 23

Si desea lidiar con valores nulos, puede hacerlo agregando una cláusula where o agregando otro COALESCE alrededor de la primera.

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

Answer 24

Si está en SQL Server 2017 o Azure, vea la respuesta de Mathieu Renda .

Tuve un problema similar cuando intentaba unir dos tablas con relaciones de uno a varios. En SQL 2005, encontré que el método XML PATH puede manejar la concatenación de las filas con mucha facilidad.

Si hay una mesa 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 concentrar las comas al principio y usar la substring para omitir la primera para que no tenga 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

Answer 25

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

Suponga que la tabla se llama NameList con una columna llamada FName,

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

devoluciones:

"Peter, Paul, Mary, "

Solo la coma extra debe ser tratada.

Edición: tal como se adoptó del comentario de @NReilingh, puede usar el siguiente método para eliminar la coma final. Asumiendo los mismos nombres de tabla y columna:

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

Answer 26

Una solución lista para usar, sin comas adicionales:

select substring(
(select '', ''+Name AS ''data()'' from Names for xml path(''''))
,3, 255) as "MyList"

Una lista vacía dará como resultado un valor NULO. Por lo general, insertará la lista en una columna de tabla o variable de programa: ajuste la longitud máxima de 255 a su necesidad.

(Diwakar y Jens Frandsen dieron buenas respuestas, pero necesitan mejoras).

Answer 27

Usa COALESCE - Aprende más desde 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

Answer 28

MySQL completo Ejemplo:

Tenemos Usuarios que pueden tener muchos Datos y queremos tener una salida, donde podamos ver todos los Datos de los usuarios en una lista:

Resultado:

___________________________
| id | rowList |
|-------------------------|
| 0 | 6, 9 |
| 1 | 1,2,3,4,5,7,8,1 |
|_________________________|

Configuración de la tabla:

CREATE TABLE `Data` (
`id` int(11) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `Data` (`id`, `user_id`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 0),
(7, 1),
(8, 1),
(9, 0),
(10, 1);
CREATE TABLE `User` (
`id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `User` (`id`) VALUES
(0),
(1);

Consulta:

SELECT User.id, GROUP_CONCAT(Data.id ORDER BY Data.id) AS rowList FROM User LEFT JOIN Data ON User.id = Data.user_id GROUP BY User.id

Answer 29

DECLARE @Names VARCHAR(8000)
SELECT @name = ''''
SELECT @Names = @Names + '','' + Names FROM People
SELECT SUBSTRING(2, @Names, 7998)

Esto pone la coma perdida al principio.

Sin embargo, si necesita otras columnas, o para CSV una tabla secundaria, necesita envolver esto en un campo escalar definido por el usuario (UDF).

También puede usar la ruta XML como una subconsulta correlacionada en la cláusula SELECT (pero tendré que esperar hasta que vuelva al trabajo porque Google no hace nada en el hogar :-)

Answer 30

SELECT STUFF((SELECT '', '' + name FROM [table] FOR XML PATH('''')), 1, 2, '''')

Aquí hay una muestra:

DECLARE @t TABLE (name VARCHAR(10))
INSERT INTO @t VALUES (''Peter''), (''Paul''), (''Mary'')
SELECT STUFF((SELECT '', '' + name FROM @t FOR XML PATH('''')), 1, 2, '''')
--Peter, Paul, Mary