Forma óptima de concatenar / agregar cadenas

Estoy encontrando una forma de agregar cadenas de diferentes filas en una sola fila. Estoy buscando hacer esto en muchos lugares diferentes, por lo que tener una function para facilitar esto sería agradable. COALESCE soluciones usando COALESCE y FOR XML , pero simplemente no me lo cortan.

La agregación de cadenas haría algo como esto:

 id | Name Result: id | Names -- - ---- -- - ----- 1 | Matt 1 | Matt, Rocks 1 | Rocks 2 | Stylus 2 | Stylus 

He echado un vistazo a las funciones agregadas definidas por CLR como reemploop de COALESCE y FOR XML , pero aparentemente SQL Azure no admite material definido por CLR, lo cual es un problema para mí porque sé que poder usarlo resolvería un problema. muchos problemas para mi

¿Hay alguna solución posible, o método similarmente óptimo (que podría no ser tan óptimo como CLR, pero oye , tomaré lo que puedo get) que puedo usar para agregar mis cosas?

SOLUCIÓN

La definición de óptimo puede variar, pero a continuación se explica cómo concatenar cadenas de diferentes filas con Transact SQL normal, que debería funcionar bien en Azure.

 ;WITH Partitioned AS ( SELECT ID, Name, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Name) AS NameNumber, COUNT(*) OVER (PARTITION BY ID) AS NameCount FROM dbo.SourceTable ), Concatenated AS ( SELECT ID, CAST(Name AS nvarchar) AS FullName, Name, NameNumber, NameCount FROM Partitioned WHERE NameNumber = 1 UNION ALL SELECT P.ID, CAST(C.FullName + ', ' + P.Name AS nvarchar), P.Name, P.NameNumber, P.NameCount FROM Partitioned AS P INNER JOIN Concatenated AS C ON P.ID = C.ID AND P.NameNumber = C.NameNumber + 1 ) SELECT ID, FullName FROM Concatenated WHERE NameNumber = NameCount 

EXPLICACIÓN

El enfoque se networkinguce a tres pasos:

  1. Numere las filas usando la agrupación OVER y PARTITION y ordere según sea necesario para la concatenación. El resultado es Partitioned CTE. Mantenemos recuentos de filas en cada partición para filtrar los resultados más tarde.

  2. Usando CTE recursivo ( Concatenated ) itere a través de los numbers de fila (columna NameNumber ) agregando valores de Name a la columna FullName .

  3. Filtra todos los resultados, pero los que tienen el NameNumber más NameNumber .

Tenga en count que para que esta consulta sea pnetworkingecible, tiene que definir tanto la agrupación (por ejemplo, en las filas de su escenario con la misma ID concatenada) como la sorting (asumí que simplemente ordera la cadena alfabéticamente antes de la concatenación).

Rápidamente probé la solución en SQL Server 2012 con los siguientes datos:

 INSERT dbo.SourceTable (ID, Name) VALUES (1, 'Matt'), (1, 'Rocks'), (2, 'Stylus'), (3, 'Foo'), (3, 'Bar'), (3, 'Baz') 

El resultado de la consulta:

 ID FullName ----------- ------------------------------ 2 Stylus 3 Bar, Baz, Foo 1 Matt, Rocks 

¿Los methods que usan FOR XML PATH como a continuación son realmente tan lentos? Itzik Ben-Gan escribe que este método tiene un buen performance en su libro de consultas T-SQL (el Sr. Ben-Gan es una fuente confiable, en mi opinión).

 create table #t (id int, name varchar(20)) insert into #t values (1, 'Matt'), (1, 'Rocks'), (2, 'Stylus') select id ,Names = stuff((select ', ' + name as [text()] from #t xt where xt.id = t.id for xml path('')), 1, 2, '') from #tt group by id 

Aunque @serge answer es correcto, pero comparé el consumo de time de su manera contra xmlpath y encontré que el xmlpath es mucho más rápido. Escribiré el código de comparación y podrás verificarlo tú mismo. Esta es la forma @serge:

 DECLARE @startTime datetime2; DECLARE @endTime datetime2; DECLARE @counter INT; SET @counter = 1; set nocount on; declare @YourTable table (ID int, Name nvarchar(50)) WHILE @counter < 1000 BEGIN insert into @YourTable VALUES (ROUND(@counter/10,0), CONVERT(NVARCHAR(50), @counter) + 'CC') SET @counter = @counter + 1; END SET @startTime = GETDATE() ;WITH Partitioned AS ( SELECT ID, Name, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Name) AS NameNumber, COUNT(*) OVER (PARTITION BY ID) AS NameCount FROM @YourTable ), Concatenated AS ( SELECT ID, CAST(Name AS nvarchar) AS FullName, Name, NameNumber, NameCount FROM Partitioned WHERE NameNumber = 1 UNION ALL SELECT P.ID, CAST(C.FullName + ', ' + P.Name AS nvarchar), P.Name, P.NameNumber, P.NameCount FROM Partitioned AS P INNER JOIN Concatenated AS C ON P.ID = C.ID AND P.NameNumber = C.NameNumber + 1 ) SELECT ID, FullName FROM Concatenated WHERE NameNumber = NameCount SET @endTime = GETDATE(); SELECT DATEDIFF(millisecond,@startTime, @endTime) --Take about 54 milliseconds 

Y esta es la forma xmlpath:

 DECLARE @startTime datetime2; DECLARE @endTime datetime2; DECLARE @counter INT; SET @counter = 1; set nocount on; declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5)) WHILE @counter < 1000 BEGIN insert into @YourTable VALUES (@counter, ROUND(@counter/10,0), CONVERT(NVARCHAR(50), @counter) + 'CC') SET @counter = @counter + 1; END SET @startTime = GETDATE(); set nocount off SELECT t1.HeaderValue ,STUFF( (SELECT ', ' + t2.ChildValue FROM @YourTable t2 WHERE t1.HeaderValue=t2.HeaderValue ORDER BY t2.ChildValue FOR XML PATH(''), TYPE ).value('.','varchar(max)') ,1,2, '' ) AS ChildValues FROM @YourTable t1 GROUP BY t1.HeaderValue SET @endTime = GETDATE(); SELECT DATEDIFF(millisecond,@startTime, @endTime) --Take about 4 milliseconds 

Para aquellos de nosotros que encontramos esto y no están utilizando la database SQL de Azure :

STRING_AGG() en PostgreSQL, SQL Server 2017 y SQL Azure
https://www.postgresql.org/docs/current/static/functions-aggregate.html
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql

GROUP_CONCAT() en MySQL
http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

(Gracias a @Brianjorder y @milanio por la actualización de Azure)

Bueno, mi antigua falta de respuesta se borró correctamente (se dejó intacta a continuación), pero si alguien aterriza aquí en el futuro, hay buenas noticias. También han implementado STRING_AGG () en la Base de datos SQL de Azure. Eso debería proporcionar la funcionalidad exacta solicitada originalmente en esta publicación con soporte nativo e integrado. @hrobky mencionó esto anteriormente como una característica de SQL Server 2016 en ese momento.

El uso es bastante simple para la request de OP:

 select id, STRING_AGG(name, ', ') as names from some_table group by id 

https://msdn.microsoft.com/en-us/library/mt790580.aspx

— Publicación anterior: No hay suficiente reputación aquí para responder a @hrobky directamente, pero STRING_AGG se ve muy bien, sin embargo, solo está disponible en SQL Server 2016 vNext actualmente. Afortunadamente, pronto seguirá Azure SQL Datababse.