How to concatenate rows with STRING_AGG in SQL Server

Combining values from several rows into a single comma-separated string is a very common need in reports, listings and data exports. For years, this required hard-to-read tricks with FOR XML PATH. Since SQL Server 2017, the STRING_AGG function solves the same problem in a single expression, with code that is simpler, faster to write and easier to maintain.

Prerequisites

• SQL Server 2017 or later, or Azure SQL Database (the STRING_AGG function is available from that version onward).
• A query tool such as SQL Server Management Studio (SSMS) or Azure Data Studio.
• Basic knowledge of SELECT and GROUP BY.

Step 1: Prepare a sample table

To follow along, we will create a small table with sales per salesperson. Each row represents a product sold by a person, with an associated numeric identifier. Run the following script on a test database — never in production:

CREATE TABLE Vendas (
    ProdutoId INT,
    Vendedor  NVARCHAR(50),
    Produto   NVARCHAR(50)
);

INSERT INTO Vendas (ProdutoId, Vendedor, Produto) VALUES
(101, 'Ana',   'Teclado'),
(102, 'Ana',   'Rato'),
(103, 'Ana',   'Webcam'),
(201, 'Bruno', 'Monitor'),
(202, 'Bruno', 'Cabo HDMI');

Notice that Ana has three products and Bruno has two. Our goal is to get just one row per salesperson, with all products gathered into a single text column.

Step 2: Concatenate values with STRING_AGG

The STRING_AGG function takes two arguments: the expression to concatenate and the separator to place between values. When we combine it with GROUP BY, each group of rows is reduced to a single text value:

SELECT
    Vendedor,
    STRING_AGG(Produto, ', ') AS Produtos
FROM Vendas
GROUP BY Vendedor;

The GROUP BY Vendedor defines the groups, and STRING_AGG joins all of each salesperson's products, separated by a comma and a space. With just four lines of SQL we get the desired result, with no subqueries or XML functions.

Step 3: Order the values with WITHIN GROUP

There is an important detail: by default, the order of the concatenated values is not guaranteed by the engine. If you need a predictable result — alphabetical order, for example — add the WITHIN GROUP clause with an ORDER BY:

SELECT
    Vendedor,
    STRING_AGG(Produto, ', ') WITHIN GROUP (ORDER BY Produto) AS Produtos
FROM Vendas
GROUP BY Vendedor;

Now the products always appear in the same order within each group. This is essential when the result is compared across runs or used in automated tests.

Step 4: Concatenate numbers and ignore null values

STRING_AGG only works on text, so numeric or date columns must first be converted with CAST or CONVERT. A very useful detail: the function automatically ignores NULL values, leaving no extra separators. Here is how to concatenate the numeric identifiers:

SELECT
    Vendedor,
    STRING_AGG(CAST(ProdutoId AS VARCHAR(10)), ';') AS Ids
FROM Vendas
GROUP BY Vendedor;

If any ProdutoId were NULL, it would simply be ignored, without creating duplicate separators or requiring manual handling.

Tip: to produce a list broken into lines instead of values side by side, use CHAR(10) as the separator. Many tools then show each item on its own line.

Verify the result

Run the query from Step 3. You should get exactly two rows, one per salesperson:

Vendedor  Produtos
--------  ---------------------------
Ana       Rato, Teclado, Webcam
Bruno     Cabo HDMI, Monitor

If you see one row per product instead of one per salesperson, then the GROUP BY is missing from the query. If the product order is not what you expected, review the WITHIN GROUP (ORDER BY ...) clause.

Conclusion

With a single function, STRING_AGG replaces complicated solutions and makes row concatenation readable and easy to maintain. From here, try combining it with WHERE filters to concatenate only part of the data, using it inside views for reports, or swapping the separator for a line break to create vertical lists. What will be the first query in your project where you replace an old FOR XML PATH with STRING_AGG?