Méthode optimale pour concaténer/agréger des chaînes de caractères

Question

Je cherche un moyen d'agréger des chaînes de caractères de différentes lignes en une seule ligne. J'ai l'intention de le faire à plusieurs endroits différents, et il serait bon de disposer d'une fonction pour faciliter cette opération. J'ai essayé des solutions utilisant COALESCE y FOR XML mais ils ne sont pas à la hauteur pour moi.

L'agrégation de chaînes de caractères donnerait quelque chose comme ça :

id | Name                    Result: id | Names
-- - ----                            -- - -----
1  | Matt                            1  | Matt, Rocks
1  | Rocks                           2  | Stylus
2  | Stylus

J'ai jeté un coup d'oeil à Fonctions agrégées définies par CLR en remplacement de COALESCE y FOR XML mais apparemment SQL Azure n'est pas ne prend pas en charge les éléments définis par le CLR, ce qui est pénible pour moi, car je sais que le fait de pouvoir les utiliser résoudrait un grand nombre de mes problèmes.

Existe-t-il une solution de contournement ou une méthode optimale similaire (qui n'est peut-être pas aussi optimale que CLR, mais hé Je prends ce que je peux avoir) que je peux utiliser pour agréger mes affaires ?

Answer 1

SOLUTION

La définition de optimal peut varier, mais voici comment concaténer des chaînes de caractères de différentes lignes à l'aide de Transact SQL ordinaire, ce qui devrait fonctionner correctement dans Azure.

;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 dbo.SourceTable
),
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

EXPLICATION

L'approche se résume à trois étapes :

1. Numérotez les lignes en utilisant OVER y PARTITION en les regroupant et en les ordonnant comme il se doit pour la concaténation. Le résultat est Partitioned CTE. Nous gardons le nombre de lignes dans chaque partition pour filtrer les résultats plus tard.

2. Utilisation de l'ETC récursif ( Concatenated ) itère à travers les numéros de ligne ( NameNumber colonne) en ajoutant Name à des valeurs FullName colonne.

3. Filtrez tous les résultats sauf ceux qui ont la plus grande valeur. NameNumber .

Gardez à l'esprit que pour rendre cette requête prévisible, il faut définir à la fois le regroupement (par exemple, dans votre scénario, les lignes avec le même ID sont concaténées) et le tri (je suppose que vous triez simplement la chaîne par ordre alphabétique avant la concaténation).

J'ai rapidement testé la solution sur SQL Server 2012 avec les données suivantes :

INSERT dbo.SourceTable (ID, Name)
VALUES 
(1, 'Matt'),
(1, 'Rocks'),
(2, 'Stylus'),
(3, 'Foo'),
(3, 'Bar'),
(3, 'Baz')

Le résultat de la requête :

ID          FullName
----------- ------------------------------
2           Stylus
3           Bar, Baz, Foo
1           Matt, Rocks

Answer 2

Les méthodes utilisant FOR XML PATH comme ci-dessous sont-elles vraiment si lentes ? Itzik Ben-Gan écrit que cette méthode a de bonnes performances dans son livre T-SQL Querying (M. Ben-Gan est une source digne de confiance, à mon avis).

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

Answer 3

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

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

(Merci à @Brianjorden et @milanio pour la mise à jour d'Azure)

Exemple de code :

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

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

Answer 4

Bien que la réponse de @serge soit correcte, j'ai comparé la consommation de temps de sa méthode à celle de xmlpath et j'ai trouvé que xmlpath est beaucoup plus rapide. Je vais écrire le code de comparaison et vous pouvez le vérifier par vous-même. C'est la méthode de @serge :

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

Et c'est la méthode xmlpath :

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

Answer 5

Mise à jour : Ms SQL Server 2017+, Azure SQL Database

Vous pouvez utiliser : STRING_AGG .

L'utilisation est assez simple pour la demande de l'OP :

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

Lire la suite

Mon ancienne non-réponse a été supprimée à juste titre (laissée intacte ci-dessous), mais si quelqu'un atterrit ici à l'avenir, il y a de bonnes nouvelles. Ils ont implémenté STRING_AGG() dans Azure SQL Database également. Cela devrait fournir la fonctionnalité exacte demandée à l'origine dans ce post avec un support natif et intégré. @hrobky l'a mentionné précédemment comme une fonctionnalité de SQL Server 2016 à l'époque.

--- Vieux poste : Pas assez de réputation ici pour répondre directement à @hrobky, mais STRING_AGG semble génial, cependant il est seulement disponible dans SQL Server 2016 vNext actuellement. Espérons qu'il suivra à Azure SQL Databse bientôt aussi bien