Split and Re-Aggregate Delimited Strings in SQL Server

Working with delimited strings in SQL Server can be messy. Maybe you inherited a table where a column holds multiple values separated by commas, or you need to take a list and break it apart before putting it back together in some aggregated form. While best practice is to normalize data into proper relational tables, sometimes you don’t control the schema. Or perhaps you’re just solving a one-off reporting need.

This article walks through how to split delimited strings into rows, process them, and then re-aggregate them back into a single string, all within SQL Server.

Splitting Delimited Strings

Since SQL Server 2016, the easiest way to split is with the built-in function STRING_SPLIT().

For example, let’s say you’ve got a table that stores product categories as a comma-delimited string:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Categories NVARCHAR(200)
);

INSERT INTO Products (ProductID, ProductName, Categories)
VALUES
(1, 'Wireless Mouse', 'Electronics,Accessories,Office'),
(2, 'Standing Desk', 'Furniture,Office'),
(3, 'Noise-Canceling Headphones', 'Electronics,Audio');

SELECT * FROM Products;

Output:

ProductID  ProductName                 Categories                    
---------  --------------------------  ------------------------------
1          Wireless Mouse              Electronics,Accessories,Office
2          Standing Desk               Furniture,Office              
3          Noise-Canceling Headphones  Electronics,Audio             

Now you want to analyze categories individually. Here’s where STRING_SPLIT() can help:

SELECT 
    p.ProductID,
    p.ProductName,
    LTRIM(RTRIM(value)) AS Category
FROM Products p
CROSS APPLY STRING_SPLIT(p.Categories, ',');

Output:

ProductID  ProductName                 Category   
---------  --------------------------  -----------
1          Wireless Mouse              Electronics
1          Wireless Mouse              Accessories
1          Wireless Mouse              Office     
2          Standing Desk               Furniture  
2          Standing Desk               Office     
3          Noise-Canceling Headphones  Electronics
3          Noise-Canceling Headphones  Audio      

This produces one row per category, which makes it much easier to filter, join, or aggregate. Notice the LTRIM()/RTRIM(). This can help clean up any unwanted whitespace. You can replace this with TRIM() if you’re using SQL Server 2017 or higher.

Re-Aggregating Values into a Delimited String

After splitting, you may need to group things back together. For example, suppose you want a list of products for each category. SQL Server’s STRING_AGG() (introduced in SQL Server 2017) handles this elegantly:

WITH Split AS (
    SELECT 
        p.ProductID,
        p.ProductName,
        TRIM(value) AS Category
    FROM Products p
    CROSS APPLY STRING_SPLIT(p.Categories, ',')
)
SELECT 
    Category,
    STRING_AGG(ProductName, ', ') AS Products
FROM Split
GROUP BY Category;

Output:

Category     Products                                  
-----------  ------------------------------------------
Accessories  Wireless Mouse                            
Audio        Noise-Canceling Headphones                
Electronics  Wireless Mouse, Noise-Canceling Headphones
Furniture    Standing Desk                             
Office       Standing Desk, Wireless Mouse             

This is essentially the reverse of what we started with. The result is one row per category, but each category now aggregates the product names back into a list.

In this example I used TRIM() instead of LTRIM() and RTRIM(), given the STRING_AGG() functionality would only work in SQL Server 2017 anyway.

A Slightly More Involved Example

Let’s say you want to generate a comma-delimited list of categories per product but sorted alphabetically. With STRING_AGG(), you can specify an order:

WITH Split AS (
    SELECT 
        p.ProductID,
        p.ProductName,
        LTRIM(RTRIM(value)) AS Category
    FROM Products p
    CROSS APPLY STRING_SPLIT(p.Categories, ',')
)
SELECT 
    ProductID,
    ProductName,
    STRING_AGG(Category, ', ') WITHIN GROUP (ORDER BY Category) AS SortedCategories
FROM Split
GROUP BY ProductID, ProductName;

Result:

ProductID  ProductName                 SortedCategories                
---------  --------------------------  --------------------------------
1          Wireless Mouse              Accessories, Electronics, Office
2          Standing Desk               Furniture, Office               
3          Noise-Canceling Headphones  Audio, Electronics              

Now the category list for each product is sorted alphabetically. For example, the categories for “Wireless Mouse” now appear as Accessories, Electronics, Office instead of in the arbitrary order stored in the table.

Things to Keep in Mind

• Performance: Splitting and re-aggregating can be expensive on large datasets. If you find yourself doing it frequently, consider redesigning the schema.
• Delimiter collisions: If your delimiter can appear in the actual data, splitting gets tricky. You may need to escape values or use a different separator.
• Older versions of SQL Server: If you’re stuck on 2014 or earlier, STRING_SPLIT() and STRING_AGG() aren’t available. You’ll need a custom split function (e.g., using XML or a numbers/tally table) and FOR XML PATH for re-aggregation.