Pregunta 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 función para facilitar esto sería agradable. He intentado 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 Funciones agregadas definidas por CLR como un reemplazo para COALESCE y FOR XML, pero aparentemente SQL Azure  no soporto cosas definidas por CLR, lo cual es un dolor para mí porque sé que poder usarlo me resolvería un montón de problemas.

¿Hay alguna solución posible, o método similarmente óptimo (que podría no ser tan óptimo como CLR, pero Oye Tomaré lo que pueda obtener) que pueda usar para agregar mis cosas.


74
2017-11-30 04:57


origen


Respuestas:


SOLUCIÓN

La definición de óptimo puede variar, pero a continuación se explica cómo concatenar cadenas de diferentes filas utilizando 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 reduce a tres pasos:

  1. Numere las filas usando OVER y PARTITION Agruparlos y ordenarlos 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) iterar a través de los números de fila (NameNumber columna) agregando Name valores a FullName columna.

  3. Filtra todos los resultados, pero los que tienen la mayor NameNumber.

Tenga en cuenta que para que esta consulta sea predecible, debe definir ambas agrupaciones (por ejemplo, en las filas de su escenario con el mismo ID están concatenados) y clasificación (asumí que simplemente ordena 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

53
2017-12-03 10:50



¿Los métodos que usan FOR XML PATH como a continuación son realmente tan lentos? Itzik Ben-Gan escribe que este método tiene un buen rendimiento en su libro de consulta 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 #t t
group by id

38
2017-12-08 22:59



Para aquellos de nosotros que encontramos esto y no están utilizando la base de datos 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 @Brianjorden y @milanio por la actualización de Azure)

Código de ejemplo:

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

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


20
2017-12-13 15:19



Aunque @serge answer es correcto, pero comparé el consumo de tiempo 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

19
2018-02-19 09:14



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 solicitud 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 antigua: 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.


1
2017-12-19 14:10



Puede usar + = para concatenar cadenas, por ejemplo:

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

si selecciona @test, le dará todos los nombres concatenados


0
2018-03-08 15:20



Encontré que la respuesta de Serge era muy prometedora, pero también encontré problemas de rendimiento con ella tal como estaba escrita. Sin embargo, cuando lo reestructuré para usar tablas temporales y no incluir tablas dobles CTE, el rendimiento pasó de 1 minuto 40 segundos a menos de 1000 registros combinados. Aquí está para cualquiera que necesite hacer esto sin FOR XML en versiones anteriores de 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
;

0
2018-05-10 05:44