Optymalny sposób łączenia / agregowania strun

Czy metody wykorzystujące ścieżkę XML, jak poniżej, są naprawdę tak powolne? Itzik Ben-Gan pisze, że ta metoda ma dobrą wydajność w jego książce zapytań T-SQL(Pan Ben-Gan jest wiarygodnym źródłem, moim zdaniem).

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

Dla tych z nas, którzy znaleźli to i nie używają Azure SQL Database :

STRING_AGG() w PostgreSQL, SQL Server 2017 i Azure SQL
https://www.postgresql.org/docs/current/static/functions-aggregate.html
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql{[10]

GROUP_CONCAT() w MySQL
http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

(dzięki @ Brianjorden i @milanio for Azure update)

Przykładowy Kod:

select Id
, STRING_AGG(Name, ', ') Names 
from Demo
group by Id

SQL: http://sqlfiddle.com/#!18/89251/1

Chociaż @ serge odpowiedź jest poprawna, ale porównałem zużycie czasu jego drogi z xmlpath i stwierdziłem, że xmlpath jest tak szybszy. Napiszę kod porównawczy, a ty sam go sprawdzisz. To jest @ serge way:

DECLARE @startTime datetime2;
DECLARE @endTime datetime2;
DECLARE @counter INT;
SET @counter = 1;

set nocount on;

declare @YourTable table (ID int, Name nvarchar(50))

WHILE @counter < 1000
BEGIN
    insert into @YourTable VALUES (ROUND(@counter/10,0), CONVERT(NVARCHAR(50), @counter) + 'CC')
    SET @counter = @counter + 1;
END

SET @startTime = GETDATE()

;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 @YourTable
),
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

SET @endTime = GETDATE();

SELECT DATEDIFF(millisecond,@startTime, @endTime)
--Take about 54 milliseconds

I to jest xmlpath sposób:

DECLARE @startTime datetime2;
DECLARE @endTime datetime2;
DECLARE @counter INT;
SET @counter = 1;

set nocount on;

declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5))

WHILE @counter < 1000
BEGIN
    insert into @YourTable VALUES (@counter, ROUND(@counter/10,0), CONVERT(NVARCHAR(50), @counter) + 'CC')
    SET @counter = @counter + 1;
END

SET @startTime = GETDATE();

set nocount off
SELECT
    t1.HeaderValue
        ,STUFF(
                   (SELECT
                        ', ' + t2.ChildValue
                        FROM @YourTable t2
                        WHERE t1.HeaderValue=t2.HeaderValue
                        ORDER BY t2.ChildValue
                        FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
                   ,1,2, ''
              ) AS ChildValues
    FROM @YourTable t1
    GROUP BY t1.HeaderValue

SET @endTime = GETDATE();

SELECT DATEDIFF(millisecond,@startTime, @endTime)
--Take about 4 milliseconds

Aktualizacja: Ms SQL Server 2017+, Azure SQL Database

Możesz użyć: STRING_AGG.

Użycie jest dość proste dla żądania OP:

SELECT id, STRING_AGG(name, ', ') AS names
FROM some_table
GROUP BY id

Czytaj Więcej

Cóż, mój stary Brak odpowiedzi został słusznie usunięty (zostawiony poniżej), ale jeśli ktoś zdarzy się tu wylądować w przyszłości, jest dobra wiadomość. Zaimplementowano również metodę STRING_AGG () w bazie danych Azure SQL. Które powinny zapewnić dokładną funkcjonalność pierwotnie żądaną w tym poście z natywne i wbudowane wsparcie. @hrobky wspomniał o tym wcześniej jako o funkcji SQL Server 2016 w tym czasie.

--- Stary Post: Nie ma tu wystarczającej reputacji, aby odpowiedzieć bezpośrednio na @hrobky, ale STRING_AGG wygląda świetnie, jednak jest obecnie dostępny tylko w SQL Server 2016 vNext. Mam nadzieję, że wkrótce pojawi się również na platformie Azure SQL Datababse..

Uznałem odpowiedź Serge ' a za bardzo obiecującą, ale napotkałem również problemy z wydajnością w formie pisemnej. Jednak, kiedy zrestrukturyzowałem go, aby używać tabel tymczasowych i nie zawierać podwójnych tabel CTE, wydajność poszła z 1 minuty 40 sekund do sub-sekundy dla 1000 połączonych rekordów. Tutaj jest dla każdego, kto musi to zrobić bez dla XML na starszych wersjach SQL Server:

DECLARE @STRUCTURED_VALUES TABLE (
     ID                 INT
    ,VALUE              VARCHAR(MAX) NULL
    ,VALUENUMBER        BIGINT
    ,VALUECOUNT         INT
);

INSERT INTO @STRUCTURED_VALUES
SELECT   ID
        ,VALUE
        ,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY VALUE) AS VALUENUMBER
        ,COUNT(*) OVER (PARTITION BY ID)    AS VALUECOUNT
FROM    RAW_VALUES_TABLE;

WITH CTE AS (
    SELECT   SV.ID
            ,SV.VALUE
            ,SV.VALUENUMBER
            ,SV.VALUECOUNT
    FROM    @STRUCTURED_VALUES SV
    WHERE   VALUENUMBER = 1

    UNION ALL

    SELECT   SV.ID
            ,CTE.VALUE + ' ' + SV.VALUE AS VALUE
            ,SV.VALUENUMBER
            ,SV.VALUECOUNT
    FROM    @STRUCTURED_VALUES SV
    JOIN    CTE 
        ON  SV.ID = CTE.ID
        AND SV.VALUENUMBER = CTE.VALUENUMBER + 1

)
SELECT   ID
        ,VALUE
FROM    CTE
WHERE   VALUENUMBER = VALUECOUNT
ORDER BY ID
;

Możesz użyć + = do łączenia łańcuchów, na przykład:

declare @test nvarchar(max)
set @test = ''
select @test += name from names

Jeśli wybierzesz @test, to wszystkie nazwy zostaną skonkatenowane