Ótima maneira de concatenar / agregar strings

Eu estou encontrando uma maneira de agregar strings de diferentes linhas em uma única linha. Eu estou procurando fazer isso em muitos lugares diferentes, então ter uma function para facilitar isso seria bom. Eu tentei soluções usando COALESCE e FOR XML , mas elas simplesmente não servem para mim.

A agregação de sequências de caracteres faria algo assim:

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

Eu dei uma olhada nas funções agregadas definidas pelo CLR como um substituto para COALESCE e FOR XML , mas aparentemente o SQL Azure não suporta material definido pelo CLR, o que é uma dor para mim porque eu sei que usá-lo resolveria um problema muitos problemas para mim.

Existe alguma alternativa possível, ou um método similarmente ótimo (que pode não ser tão bom quanto o CLR, mas hey eu vou pegar o que eu posso conseguir) que eu possa usar para agregar minhas coisas?

SOLUÇÃO

A definição de ótimo pode variar, mas eis como concatenar cadeias de caracteres de linhas diferentes usando o Transact SQL regular, que deve funcionar bem no 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 

EXPLICAÇÃO

A abordagem resume-se a três etapas:

  1. Numere as linhas usando o agrupamento OVER e PARTITION e ordenando-as conforme necessário para a concatenação. O resultado é CTE Partitioned . Mantemos contagens de linhas em cada partição para filtrar os resultados mais tarde.

  2. Usando CTE recursivo ( Concatenated ) percorra os números de linha (coluna NameNumber ) adicionando valores de Name à coluna FullName .

  3. Filtre todos os resultados, exceto aqueles com o maior número de NameNumber .

Lembre-se de que, para tornar essa consulta previsível, é necessário definir tanto o agrupamento (por exemplo, em suas linhas de cenário com o mesmo ID são concatenados) quanto a sorting (presumo que você simplesmente classifique a sequência alfabeticamente antes da concatenação).

Eu testei rapidamente a solução no SQL Server 2012 com os seguintes dados:

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

O resultado da consulta:

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

Os methods usando FOR XML PATH, como abaixo, são realmente lentos? Itzik Ben-Gan escreve que este método tem um bom desempenho em seu livro T-SQL Querying (o Sr. Ben-Gan é uma fonte confiável, na minha opinião).

 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 

Para aqueles de nós que acharam isso e não estão usando o Banco de Dados SQL do Azure :

STRING_AGG() no PostgreSQL, SQL Server 2017 e SQL do Azure
https://www.postgresql.org/docs/current/static/functions-aggregate.html
https://docs.microsoft.com/pt-br/sql/t-sql/functions/string-agg-transact-sql

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

(Graças a @Brianjorden e @milanio para atualização do Azure)

Exemplo de código:

 select Id , STRING_AGG(Name, ', ') Names from Demo group by Id 

SQL Fiddle: http://sqlfiddle.com/#!18/89251/1

Embora a resposta @serge esteja correta, mas eu comparei o consumo de tempo do seu caminho com o xmlpath e descobri que o xmlpath é muito mais rápido. Vou escrever o código de comparação e você pode verificar por si mesmo. Esta é a maneira @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 

E esta é a maneira 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 

Bem, minha velha não-resposta foi excluída por direito (deixada no tato abaixo), mas se alguém chegar aqui no futuro, há boas notícias. Eles implementaram STRING_AGG () no Banco de Dados SQL do Azure também. Isso deve fornecer a funcionalidade exata originalmente solicitada neste post com suporte nativo e integrado. @hrobky mencionou isso anteriormente como um recurso do SQL Server 2016 no momento.

O uso é bem simples para o pedido do OP:

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

https://msdn.microsoft.com/pt-br/library/mt790580.aspx

— Post antigo: Não há reputação suficiente aqui para responder diretamente ao @hrobky, mas o STRING_AGG parece ótimo, no entanto, ele só está disponível no SQL Server 2016 vNext atualmente. Espero que isso também se siga no Azure SQL Datababse.

Você pode usar + = para concatenar strings, por exemplo:

 declare @test nvarchar(max) set @test = '' select @test += name from names 

Se você selecionar @test, ele lhe dará todos os nomes concatenados

Eu achei a resposta de Serge muito promissora, mas também encontrei problemas de performance com ela como escrita. No entanto, quando o reutilizei para usar tabelas temporárias e não include tabelas CTE duplas, o desempenho passou de 1 minuto 40 segundos para sub-segundo para 1000 registros combinados. Aqui está para qualquer pessoa que precise fazer isso sem o FOR XML em versões mais antigas do SQL Server:

 DECLARE @STRUCTURED_VALUES TABLE ( ID INT ,VALUE VARCHAR(MAX) NULL ,VALUENUMBER BIGINT ,VALUECOUNT INT ); INSERT INTO @STRUCTURED_VALUES SELECT ID ,VALUE ,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY VALUE) AS VALUENUMBER ,COUNT(*) OVER (PARTITION BY ID) AS VALUECOUNT FROM RAW_VALUES_TABLE; WITH CTE AS ( SELECT SV.ID ,SV.VALUE ,SV.VALUENUMBER ,SV.VALUECOUNT FROM @STRUCTURED_VALUES SV WHERE VALUENUMBER = 1 UNION ALL SELECT SV.ID ,CTE.VALUE + ' ' + SV.VALUE AS VALUE ,SV.VALUENUMBER ,SV.VALUECOUNT FROM @STRUCTURED_VALUES SV JOIN CTE ON SV.ID = CTE.ID AND SV.VALUENUMBER = CTE.VALUENUMBER + 1 ) SELECT ID ,VALUE FROM CTE WHERE VALUENUMBER = VALUECOUNT ORDER BY ID ; 
    Intereting Posts