String aggregation

Combine several rows into a single string: two nested loops versus one aggregation.

≈ 2 min readReading

On this page

Problem statement
Data set
Procedural versus set-based
1. Procedural
2. Set-based
Expected result
Why set-based is preferable
Pitfalls
Variants
Verifying equivalence
SQL Server versions

Problem statement#

For each order, produce a single string listing its products, sorted alphabetically and separated by a comma.

Data set#

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');

Procedural versus set-based#

Procedural#

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;

Set-based#

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;

Expected result#

CommandeId	Produits
1	Cahier, Gomme, Stylo
2	Règle

Why set-based is preferable#

Concatenation is an aggregation like any other: it is done in a GROUP BY, in a single pass.
The procedural version nests two cursors: one per order, one per order line.
WITHIN GROUP (ORDER BY …) makes the concatenation order explicit and guaranteed.

Pitfalls#

Truncation: if the expression is not of type nvarchar(max) / varchar(max), the result is limited to 4,000 (nvarchar) or 8,000 (varchar) characters. Hence the CAST … AS nvarchar(max).
NULL: NULL values are ignored (and the corresponding separator is not added). Use ISNULL to display a replacement value.
Accumulation in a variable (SELECT @s = @s + col …): processing order is not guaranteed, especially with ORDER BY; Microsoft documentation rules it out for ordered cases. Do not rely on it.
Function not available before SQL Server 2017: see the variant.

Variants#

-- Before SQL Server 2017: FOR XML PATH (TYPE + .value to avoid escaping & < >)
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;

Verifying equivalence#

A change of method is only worthwhile if the result is identical. Compare in both directions; no rows should come out.

-- Run in the same batch as the procedural version (the @Resultat variable must exist)
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;
-- Then the reverse. No rows expected.

SQL Server versions#

STRING_AGG: SQL Server 2017 and later; WITHIN GROUP with compatibility level 110 or higher. FOR XML PATH: all modern versions.