Convert SQL Server result set into string

sql

string-concatenation

sql-server

data-types

byNikita Barsukov·Dec 12, 2024

SQL Server 2017+ offers a built-in function called STRING_AGG to conveniently concatenate rows with a defined separator:

-- Use STRING_AGG for easy-peasy lemon-squeezy concatenation
SELECT STRING_AGG(YourColumn, ', ') AS CombinedString FROM YourTable;

However, for the brave souls who're stuck with an older version, we have FOR XML PATH in our armory to perform the same task:

-- FOR XML PATH is like STRING_AGG's granddaddy. Still works like a charm though!
SELECT STUFF((SELECT ', ' + CAST(YourColumn AS NVARCHAR(MAX)) FROM YourTable FOR XML PATH('')), 1, 2, '') AS CombinedString;

Starting right and type conversion

In the realm of string concatenation, especially in scenarios dealing with brutally large data sets, one must learn to initialize variables accurately. Also, respect the data types; don't mix them up like a bad cocktail!

Use NVARCHAR(MAX) while declaring variables. Unlike VARCHAR, it won't leave your precious data hanging halfway.
Show love to CAST and CONVERT. They're the secret sauce that turns non-string data into string before concatenation.

Concatenating in older versions

For those sporting outdated versions, you've two weapons: STUFF and FOR XML PATH. They're old but gold:

-- Declare @ConcatenatedString, the unsung hero that'll hold our result
DECLARE @ConcatenatedString NVARCHAR(MAX);

-- Don't forget STUFF. It's busy removing the unwanted leading comma.
SELECT @ConcatenatedString = STUFF(
    (SELECT ', ' + CAST(StudentId AS NVARCHAR(10))
     FROM Students
     FOR XML PATH('')
    ),
    1, 2, '');

-- FINALLY! Thine result hath cometh.
SELECT @ConcatenatedString;

ISNULL or COALESCE are your knights in shining armor, preventing NULL values from ruining your concatenation party.

Handling every scenario like a pro

Fancy challenges like stray commas or inconsistent data types? Here's how you tackle it like a boss:

Last comma problem: Use LEFT with LEN to give that trailing comma a pink slip.
Filtering dilemma: Add a WHERE clause to act as a bouncer for your specific needs.
Dealing with NULLs: Put ISNULL or COALESCE in your SELECT statement -- they chew up NULLs for breakfast.
Code reusability: Get a patent for your logic by sticking it into a stored procedure. Then sit back and let output parameters do all the hard work.

More ways to play with concatenation

Let's pop the hood and see what other cool functionalities SQL Server concatenation offers:

Structured output: Remember FORMAT function? It's the tailor who ensures all the numbers or dates in your string fit perfectly.
Hello, exact concatenation: CONCAT function does exactly what it says on the tin. Available from SQL Server 2012 onwards!
Caring for performance: Got a big dataset? Tread carefully. Large concatenations can make your server sweat!