Concatenate multiple rows from a subquery into a string in SQL Server

In this post, we are gonna see how to concatenate multiple rows into one single text string in MS SQL Server.

You have two tables:

Table products:

item_id	item_name
1	Skirt A
2	Skirt B

And table stores:

store_id	item_id	state_code
1	1	CA
2	1	CO
3	1	AK
4	2	GA
5	2	AZ
6	2	TX

Then you want to return the result where state_code is being concatenated into a strings like this:

item_id	item_name	state_code
1	Skirt A	CA, CO, AK
2	Skirt B	GA, AZ, TX

• In SQL Server 2005, you could use the FOR XML PATH command:

SELECT item_id,
       item_name,
       (STUFF((SELECT CAST(', ' + state_code AS VARCHAR(MAX)) 
         FROM stores
         WHERE (item_id = products.item_id) 
         FOR XML PATH ('')), 1, 2, '')) AS state_code
FROM products;

• In SQL Server 2017+ and SQL Azure, you can concatenate using STRING_AGG:

SELECT
    [item_id],
    STRING_AGG([state_code], ', ') AS state_code
FROM
    stores
GROUP BY
    item_id;

---

Need a good GUI Tool for MS SQL Server? Try TablePlus, a modern, native tool for multiple databases including SQL Server, MySQL, PostgreSQL, SQLite, etc. And it’s free to use for as long as you need it to.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS