string_agg - stuff select sql server

Cómo concatenar todas las cadenas de una columna determinada para cada grupo

Question

Supongamos que tengo esta tabla [Table1]

Name    Mark
------- ------
ABC     10
DEF     10
GHI     10
JKL     20
MNO     20
PQR     30

¿Cuál debería ser mi declaración SQL para recuperar un registro que se parece a esto: (agrupar por [marca]). He hecho las columnas 1 y 2, pero no sé cómo lograr la tercera columna (concat la [nombre] con la misma [marca])

mark count     names
---- -----     -----------
10       3     ABC,DEF,GHI
20       2     JKL,MNO
30       1     PQR

Estoy usando Microsoft SQL. Por favor ayuda. Gracias

Answer 1

Aquí hay una respuesta relacionada con el rendimiento!

http://jerrytech.blogspot.com/2010/04/tsql-concatenate-strings-1-2-3-and.html

El uso de funciones XML en una consulta grande es un asesino de rendimiento.

Usar un CTE es una superestrella del rendimiento.

Echa un vistazo al enlace, te explicará cómo.

Admito que el trabajo para lograrlo es más.

Pero el resultado es milisegundos en millones de filas.

Answer 2

Basado libremente en Itzik Ben-Gan, Dentro de Microsoft SQL Server 2005: Programación T-SQL , pág. 215:

IF OBJECT_ID(''dbo.Table1'') IS NOT NULL 
    DROP TABLE dbo.Table1 ;
GO
CREATE TABLE dbo.Table1 ( Name VARCHAR(10), Mark INT ) ;

INSERT INTO dbo.Table1 ( Name, Mark ) VALUES ( ''ABC'',     10 ) ;
INSERT INTO dbo.Table1 ( Name, Mark ) VALUES ( ''DEF'',     10 ) ;
INSERT INTO dbo.Table1 ( Name, Mark ) VALUES ( ''GHI'',     10 ) ;
INSERT INTO dbo.Table1 ( Name, Mark ) VALUES ( ''JKL'',     20 ) ;
INSERT INTO dbo.Table1 ( Name, Mark ) VALUES ( ''MNO'',     20 ) ;
INSERT INTO dbo.Table1 ( Name, Mark ) VALUES ( ''PQR'',     30 ) ;


WITH DelimitedNames AS
(
    SELECT Mark, T2.Count,
        (   SELECT Name + '','' AS [text()]
            FROM dbo.Table1 AS T1
            WHERE T1.Mark = T2.Mark
            ORDER BY T1.Mark
            FOR XML PATH('''')) AS Names
    FROM ( SELECT Mark, COUNT(*) AS Count FROM dbo.Table1 GROUP BY Mark ) AS T2 
)
SELECT Mark, Count, LEFT(Names, LEN(NAMES) - 1) AS Names
FROM DelimitedNames ;

Answer 3

La solución de polishchuks es más elegante, pero esto es básicamente lo mismo, solo tratamos la coma final de manera diferente.

CREATE TABLE #Marks(Name nchar(3), Mark int)

INSERT INTO #Marks

SELECT ''ABC'', 10 UNION ALL
SELECT ''DEF'', 10 UNION ALL
SELECT ''GHI'', 10 UNION ALL
SELECT ''JKL'', 20 UNION ALL
SELECT ''MNO'', 20 UNION ALL
SELECT ''PQR'', 30 


SELECT 
    mark,  
    [count],
    CASE WHEN Len(Names) > 0 THEN LEFT(Names, LEN(Names) -1) ELSE '''' END names  
    FROM
(
SELECT
    Mark,
    COUNT(Mark) AS [count], 
        (
        SELECT DISTINCT 
            Name + '', ''
        FROM 
            #Marks M1
        WHERE M1.Mark = M2.Mark
        FOR XML PATH('''')    
        ) Names 
FROM #Marks M2
GROUP BY Mark
) M

Answer 4

Si MS SQL 2005 o superior.

declare @t table([name] varchar(max), mark int)

insert @t values (''ABC'', 10), (''DEF'', 10), (''GHI'', 10),
    (''JKL'', 20), (''MNO'', 20), (''PQR'', 30)


select t.mark, COUNT(*) [count]
    ,STUFF((
        select '','' + [name]
        from @t t1
        where t1.mark = t.mark
        for xml path(''''), type
    ).value(''.'', ''varchar(max)''), 1, 1, '''') [values]
from @t t
group by t.mark

Salida:

mark        count       values
----------- ----------- --------------
10          3           ABC,DEF,GHI
20          2           JKL,MNO
30          1           PQR