How to use GROUP BY to concatenate strings in SQL Server?

sql

string-aggregation

sql-server

concatenation

byAlex Kataev·Oct 7, 2024

For string concatenation using grouped data in SQL Server, use STRING_AGG() in SQL Server 2017+:

-- Get everyone singing same tune in 2017 and beyond
SELECT yourGroupCol, STRING_AGG(yourStrCol, ', ') AS concatStr
FROM yourTbl
GROUP BY yourGroupCol;

For the pre-2017 crowd, FOR XML PATH and STUFF() come to the rescue:

-- When you need the old skool remix
SELECT 
  yourGroupCol, 
  STUFF((
    SELECT ', ' + yourStrCol
    FROM yourTbl
    WHERE yourTbl.yourGroupCol = outerTbl.yourGroupCol
    FOR XML PATH('')
  ), 1, 2, '') AS concatStr
FROM yourTbl AS outerTbl
GROUP BY yourGroupCol;

Pre-2017 concatenation quirks

The classics never go out of style! FOR XML PATH and STUFF() have got your back in SQL Server versions 2005 onwards. Efficiently merging values with FOR XML PATH('') preps the stage for string concatenation.

The STUFF() function elegantly strips away that extra comma - because nobody likes a misplaced comma, right programmers? 😅 To keep the ball rolling, nest it within an inner SELECT bonded on ID.

And yes, the subtle .value() function ensures our XML entities are strictly on point, making your SQL concatenation robust and reliable.

Old tricks for old methods

Don't forget these tips when you're rolling with the old-timers:

Stylish separators: Jazz up your output with custom delimiters.
Handling large strings: Don't choke on long strings - use VARCHAR(MAX).
Dealing with special characters: FOR XML PATH got you covered, ensuring special characters don't disrupt the grand concert.

Enter STRING_AGG: The modern maestro

SQL Server 2017 brought with it the power of STRING_AGG(). Say goodbye to managing XML paths and stuff, this star function does it all:

STRING_AGG() makes the magic happen within complex queries, arranging your data like a pro conductor.
Customizable and flexible, STRING_AGG() jams perfectly with any delimiter.
Rock on with consistent type casting, courtesy of VARCHAR.

Dealing with possible hiccups

Beware potential bumps on the road to perfect concatenation:

Duplicate strings: Use DISTINCT to perform your unique solo.
Sorting the output: Control the beat with ORDER BY.
Performance tuning: Keep an eye on execution plans and indexes - don't let your composition be a one-hit wonder.

Advanced scenarios

When the ordinary just won't cut it:

Hierarchical data: Concatenate strings to show off your groovy tree-like structures.
Creating CSV outputs: Forge your data into exportable CSV formats.
Debugging: Whip up a quick log right from the result of a query. Who said debugging can't be fun?

explain-codes / Sql / How to use GROUP BY to concatenate strings in SQL Server?

Linked

Optimal way to concatenate/aggregate strings



Simulating group_concat MySQL function in Microsoft SQL Server 2005?



Group BY to combine/concat a column



How to Concatenate Text from Multiple Rows into a Single Text String in SQL Server



T-sql split string

