Saltar al contenido

La concatenación de filas SQL con XML PATH y STUFF da un error SQL agregado

Queremos darte la mejor respuesta que hallamos en todo internet. Queremos que te sirva de ayuda y si deseas comentarnos algún detalle que nos pueda ayudar a perfeccionar nuestra información hazlo con libertad.

Solución:

Lo siento, perdí un paso en la relación. Pruebe esta versión (aunque la de Martin también funcionará):

SELECT DISTINCT o.section, names= STUFF((
    SELECT ', ' + b.Name 
    FROM dbo.TableA AS a
    INNER JOIN dbo.TableB AS b
    ON a.AccountId = b.AccountId
    WHERE a.Section = o.Section
    FOR XML PATH, TYPE).value(N'.[1]', N'varchar(max)'), 1, 2, '')
FROM dbo.TableA AS o;

Un enfoque que es al menos tan bueno, pero a veces mejor, es cambiar de DISTINCT para GROUP BY:

SELECT o.section, names= STUFF((
    SELECT ', ' + b.Name 
    FROM dbo.TableA AS a
    INNER JOIN dbo.TableB AS b
    ON a.AccountId = b.AccountId
    WHERE a.Section = o.Section
    FOR XML PATH, TYPE).value(N'.[1]', N'varchar(max)'), 1, 2, '')
FROM dbo.TableA AS o
GROUP BY o.section;

En un nivel alto, la razón DISTINCT se aplica a toda la lista de columnas. Por lo tanto, para cualquier duplicado, debe realizar el trabajo agregado para cada duplicado antes de aplicar. DISTINCT. Si utiliza GROUP BY entonces potencialmente puede eliminar duplicados antes de haciendo cualquiera de los trabajos de agregación. Este comportamiento puede variar según el plan dependiendo de una variedad de factores, incluidos índices, estrategia del plan, etc. Y un cambio directo a GROUP BY puede que no sea posible en todos los casos.

En cualquier caso, ejecuté ambas variaciones en SentryOne Plan Explorer. Los planes son diferentes en algunas formas menores y poco interesantes, pero la E / S involucrada con la mesa de trabajo subyacente es reveladora. Aquí está DISTINCT:

ingrese la descripción de la imagen aquí

Y aqui esta GROUP BY:

ingrese la descripción de la imagen aquí

Cuando hice las tablas más grandes (más de 14,000 filas mapeadas a 24 valores potenciales), esta diferencia es más pronunciada. DISTINCT:

ingrese la descripción de la imagen aquí

GROUP BY:

ingrese la descripción de la imagen aquí

En SQL Server 2017, puede usar STRING_AGG:

SELECT a.section, STRING_AGG(b.Name, ', ')
    FROM dbo.TableA AS a
    INNER JOIN dbo.TableB AS b
    ON a.AccountId = b.AccountId
    WHERE a.Section = a.Section
    GROUP BY a.section;

La E / S aquí es casi nada:

ingrese la descripción de la imagen aquí


Pero, si no está en SQL Server 2017 (o Azure SQL Database) y no puede usar STRING_AGG, Tengo que dar crédito donde es debido… La respuesta de Paul White a continuación tiene muy poca E / S y le quita los pantalones a ambos FOR XML PATH soluciones anteriores.

ingrese la descripción de la imagen aquí


Otras mejoras de estas publicaciones:

  • Concatenación agrupada en SQL Server
  • Concatenación agrupada: ordenar y eliminar duplicados
  • Comparando string métodos de división / concatenación

Ver también:

  • Malos hábitos que dejar: evitar el esquema prefix

Pensé que probaría una solución usando XML.

Demostración de SEDE

Mesas

DECLARE @TableA AS table
(
    ID integer PRIMARY KEY,
    Section varchar(10) NOT NULL,
    AccountID char(2) NOT NULL
);

DECLARE @TableB AS table
(
    AccountID char(2) PRIMARY KEY,
    Name varchar(20) NOT NULL
);

Datos

INSERT @TableA
    (ID, Section, AccountID)
VALUES
    (1, 'shoes', 'A1'),
    (2, 'shoes', 'A2'),
    (3, 'shoes', 'A3'),
    (4, 'books', 'A1');

INSERT @TableB
    (AccountID, Name)
VALUES
    ('A1', 'AccountName1'),
    ('A2', 'AccountName2'),
    ('A3', 'AccountName3');

Unirse y convertir a XML

DECLARE @x xml =
(
    SELECT
        TA.Section,
        CA.Name
    FROM @TableA AS TA
    JOIN @TableB AS TB
        ON TB.AccountID = TA.AccountID
    CROSS APPLY
    (
        VALUES(',' + TB.Name)
    ) AS CA (Name)
    ORDER BY TA.Section
    FOR XML AUTO, TYPE, ELEMENTS, ROOT ('Root')
);

Consulta de creación XML

El XML de la variable se ve así:


  
    
shoes
,AccountName1 ,AccountName2 ,AccountName3
books
,AccountName1

Consulta

La consulta final tritura el XML en secciones y concatena los nombres en cada una:

SELECT
    Section = 
        N.n.value('(./Section/text())[1]', 'varchar(10)'),
    Names = 
        STUFF
        (
            -- Consecutive text nodes collapse
            N.n.query('./CA/Name/text()')
            .value('./text()[1]', 'varchar(8000)'), 
            1, 1, ''
        )
-- Shred per section
FROM @x.nodes('Root/TA') AS N (n);

Resultado

Producción

Plan de ejecución

Plan de ejecución

Más adelante puedes encontrar las críticas de otros programadores, tú asimismo eres capaz insertar el tuyo si lo deseas.

¡Haz clic para puntuar esta entrada!
(Votos: 0 Promedio: 0)



Utiliza Nuestro Buscador

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *