Manera óptima de concatenar/agregar cadenas

Estoy encontrando una manera de agregar cadenas de diferentes filas en una sola fila. Estoy buscando hacer esto en muchos lugares diferentes, por lo que sería bueno tener una función para facilitar esto. He probado soluciones usando COALESCEyFOR XML , pero simplemente no me sirven.

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 reemplazo de COALESCEy FOR XML, pero aparentemente SQL Azure no admite cosas definidas por CLR, lo cual es una molestia para mí porque sé que poder usarlo resolvería muchos problemas. problemas para mi.

¿Existe alguna solución alternativa posible o un método óptimo similar (que puede que no sea tan óptimo como CLR, pero tomaré lo que pueda conseguir) que pueda usar para agregar mis cosas?

Nov 30 '12 11:11 matt

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:

Numere las filas utilizándolas OVER, PARTITIONagrupándolas y ordenándolas según sea necesario para la concatenación. El resultado es PartitionedCTE. Mantenemos recuentos de filas en cada partición para filtrar los resultados más adelante.
Usando CTE recursivo ( Concatenated), itere a través de los números de fila ( NameNumbercolumna) agregando Namevalores a FullNamela columna.
Filtra todos los resultados excepto los que tienen el nivel más alto NameNumber.

Tenga en cuenta que para que esta consulta sea predecible, es necesario definir tanto la agrupación (por ejemplo, en su escenario, las filas con las mismas IDestán concatenadas) como la clasificación (supuse que simplemente ordena la cadena alfabéticamente antes de la concatenación).

Probé rápidamente 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

Dec 03 '2012 10:12 Serge Belov

¿Los métodos que utilizan FOR XML PATH como los siguientes son realmente tan lentos? Itzik Ben-Gan escribe que este método tiene un buen rendimiento en su libro T-SQL Querying (en mi opinión, el Sr. Ben-Gan es una fuente confiable).

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

Dec 08 '2012 22:12 slachterman