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

Digging deeper with STRING_AGG

For grouping purposes, we concatenate values associated with a particular key using GROUP BY statement:

-- Because birds of a feather flock together 🐦
SELECT SubjectID, STRING_AGG(Name, ', ') 
FROM Students
GROUP BY SubjectID;


Need to order names alphabetically in the string? No problem!

-- Have it arranged like an old school phonebook
SELECT SubjectID, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) 
FROM Students
GROUP BY SubjectID;


Pre-2017? FOR XML PATH to the rescue

If you live in the past with SQL Server versions below 2017 leaning on FOR XML PATH is the way to go:

-- For all the SQL Server oldies out there 🙌 
SELECT SubjectID, 
       STUFF((SELECT ', ' + Name
        FROM Students
        WHERE (SubjectID = Results.SubjectID)
        FOR XML PATH(''), TYPE
        ).value('.','NVARCHAR(MAX)'), 1, 2, '')
FROM (SELECT DISTINCT SubjectID FROM Students) Results;


The STUFF function removes the unwanted comma at the start, .value ensures spot-on conversion from XML.

The magic wand: COALESCE for string concatenation

COALESCE comes to the rescue when building a dynamic string within a select statement:

-- Abracadabra! 🪄 Dynamic string, appear!
DECLARE @students NVARCHAR(MAX);
SELECT @students = COALESCE(@students + ', ', '') + Name
FROM Students;


This creates the collated string list of all students.

Wrangling null values and whitespace

Are NULL entries proving a nuisance? Consider them handled:

-- A null by any other name would smell as sweet
SELECT STRING_AGG(ISNULL(Name, 'N/A'), ', ') FROM Students;


Loathe messy white spaces? Let's sweep them under the mat:

-- This is some next level Marie Kondo tidying up 🧹
SELECT STRING_AGG(LTRIM(RTRIM(Name)), ', ') FROM Students;


Enhancements: Performance optimization

For those living in the fast lane, with complex data sets, performance-tuned tweaks are key:

• Filtered indexes or temporary tables to reduce data before concatenation.
• Batch selections over one large concatenation for weighty data sets.

Tricks of the trade

1. Delimiters in values: Employ REPLACE if your column values may contain the delimiter:

-- This is the part where we play hide and seek 🙈🙉 
SELECT STRING_AGG(REPLACE(Name, ',', ';'), ', ') FROM Students;


2. JSON methods: SQL Server 2016 offers a different toy box, JSON functions:

-- Who needs glue when you've got JSON 🧩 
SELECT SubjectID, 
       REPLACE((SELECT Name AS "item" FROM Students FOR JSON PATH), '{"item":"', '') AS Names
FROM Students
GROUP BY SubjectID;


Using .value to cast XML values

Working with XML data calls for casting. The .value method does the trick in SQL Server 2005:

-- A wild XML appeared! .value used convert. It's super effective! 🎮
SELECT (SELECT Name + ', ' 
        FROM Students
        FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)');


This ensures apt nVarChar conversions from XML.

Prefixing with unique identifiers

For distinguishment enthusiasts, unique identifiers prefix to the rescue:

-- Badge, number and name, please! 🎫
SELECT STRING_AGG(CAST(ID AS NVARCHAR(10)) + ':' + Name, ', ') FROM Students;


Bypassing character encoding issues

Non-standard characters can be disruptive. Here's a detour:

-- Because a clean SQL query is a happy SQL query 🧼
SELECT STRING_AGG(Name, ', ') FROM Students 
WHERE Name NOT LIKE '%[^a-zA-Z0-9 ,]%'


Uses regex-like patterns to exclude anomalous characters.