i

Description

Un collier de perles, des valeurs mises bout à bout

Source

auteur indiqué sur la page source · page d’origine

Licence

CC0 Domaine public

Photo d’origine, non retouchée. L’arrière-plan en est une version stylisée par le site.

Agrégation de texte

Regrouper plusieurs lignes en une seule chaîne : deux boucles imbriquées contre une agrégation.

Sur cette page

1. Énoncé
2. Jeu de données
3. Procédural contre ensembliste
   1. Procédural
   2. Ensembliste
4. Résultat attendu
5. Pourquoi l'ensembliste est préférable
6. Pièges
7. Variantes
8. Vérifier l'équivalence
9. Versions de SQL Server

Énoncé#

Pour chaque commande, produire une chaîne unique listant ses produits, triés par ordre alphabétique et séparés par une virgule.

Jeu de données#

CREATE TABLE dbo.LignesCommande
(
    CommandeId int           NOT NULL,
    Produit    nvarchar(100) NOT NULL
);

INSERT INTO dbo.LignesCommande (CommandeId, Produit)
VALUES (1, N'Stylo'), (1, N'Cahier'), (2, N'Règle'), (1, N'Gomme');

Procédural contre ensembliste#

DECLARE @Resultat TABLE (CommandeId int PRIMARY KEY, Produits nvarchar(max));
DECLARE @Id int, @Produit nvarchar(100), @Liste nvarchar(max);

DECLARE cmd CURSOR LOCAL FAST_FORWARD FOR
    SELECT DISTINCT CommandeId FROM dbo.LignesCommande;
OPEN cmd;
FETCH NEXT FROM cmd INTO @Id;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Liste = NULL;

    DECLARE lig CURSOR LOCAL FAST_FORWARD FOR
        SELECT Produit FROM dbo.LignesCommande WHERE CommandeId = @Id ORDER BY Produit;
    OPEN lig;
    FETCH NEXT FROM lig INTO @Produit;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Liste = CASE WHEN @Liste IS NULL THEN @Produit ELSE @Liste + N', ' + @Produit END;
        FETCH NEXT FROM lig INTO @Produit;
    END;
    CLOSE lig;
    DEALLOCATE lig;

    INSERT INTO @Resultat (CommandeId, Produits) VALUES (@Id, @Liste);
    FETCH NEXT FROM cmd INTO @Id;
END;
CLOSE cmd;
DEALLOCATE cmd;

SELECT * FROM @Resultat ORDER BY CommandeId;

SELECT CommandeId,
       STRING_AGG(CAST(Produit AS nvarchar(max)), N', ') WITHIN GROUP (ORDER BY Produit) AS Produits
FROM   dbo.LignesCommande
GROUP BY CommandeId
ORDER BY CommandeId;

Résultat attendu#

Pourquoi l'ensembliste est préférable#

• La concaténation est une agrégation comme une autre : elle se fait dans un GROUP BY, en une seule passe.
• La version procédurale emboîte deux curseurs : un par commande, un par ligne de commande.
• WITHIN GROUP (ORDER BY …) rend l'ordre de concaténation explicite et garanti.

Pièges#

• Troncature : si l'expression n'est pas de type nvarchar(max) / varchar(max), le résultat est limité à 4 000 (nvarchar) ou 8 000 (varchar) caractères. D'où le CAST … AS nvarchar(max).
• NULL : les valeurs NULL sont ignorées (et le séparateur correspondant n'est pas ajouté). Utiliser ISNULL pour afficher une valeur de remplacement.
• Accumulation dans une variable (SELECT @s = @s + col …) : ordre de traitement non garanti, notamment avec ORDER BY ; la documentation Microsoft l'écarte pour les cas ordonnés. Ne pas s'y fier.
• Fonction non disponible avant SQL Server 2017 : voir la variante.

Variantes#

-- Avant SQL Server 2017 : FOR XML PATH (TYPE + .value pour éviter l'échappement de & < >)
SELECT c.CommandeId,
       STUFF((SELECT N', ' + l.Produit
              FROM   dbo.LignesCommande AS l
              WHERE  l.CommandeId = c.CommandeId
              ORDER BY l.Produit
              FOR XML PATH(N''), TYPE).value(N'.', N'nvarchar(max)'), 1, 2, N'') AS Produits
FROM  (SELECT DISTINCT CommandeId FROM dbo.LignesCommande) AS c;

Vérifier l'équivalence#

Un changement de méthode ne vaut que si le résultat est identique. Comparer dans les deux sens ; aucune ligne ne doit sortir.

-- À exécuter dans le même lot que la version procédurale (la variable @Resultat doit exister)
SELECT CommandeId, Produits FROM @Resultat
EXCEPT
SELECT CommandeId, STRING_AGG(CAST(Produit AS nvarchar(max)), N', ') WITHIN GROUP (ORDER BY Produit)
FROM dbo.LignesCommande GROUP BY CommandeId;
-- Puis l'inverse. Aucune ligne attendue.

Versions de SQL Server#

STRING_AGG : SQL Server 2017 et ultérieur ; WITHIN GROUP avec le niveau de compatibilité 110 ou plus. FOR XML PATH : toutes les versions modernes.