Как объединить все строки из определенного столбца для каждой группы

Question

Предположим, у меня есть эта таблица [Table1]

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

Каким должен быть мой SQL-оператор для извлечения записи, которая выглядит следующим образом: (group by [mark]). Я сделал столбцы 1 и 2, но не знаю, как выполнить третий столбец (объединить [имя] с тем же [знаком])

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

Я использую Microsoft SQL. Пожалуйста помоги. Спасибо

Answer 1

Если MS SQL 2005 или выше.

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

---

Выход:

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

Answer 2

Вот ответ, связанный с производительностью!

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

Использование функций XML в большом запросе снижает производительность.

Использование CTE - суперзвезда производительности.

Проверьте ссылку, это объяснит, как.

Я допускаю работу, чтобы выполнить это больше.

Но результат составляет миллисекунды по миллионам строк.

Answer 3

Решение для polishchuks более элегантно, но это в основном одно и то же, мы просто по-разному обращаемся с запятой.

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

Свободно основано на Ицик Бен-Ган, Inside Microsoft SQL Server 2005: Программирование на T-SQL, с. 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 ;