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

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

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

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

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

Я использую Microsoft SQL. Пожалуйста помоги. Благодаря

Ответ 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

Ответ 2

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

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

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

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

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

Я признаю, что работа над этим выполнена.

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

Ответ 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

Ответ 4

На основе Итзика Бен-Гана, внутри Microsoft SQL Server 2005: T-SQL Programming, p. 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 ;