Starting with SQL Server 2017, you can now make your query results appear as a list. This means you can have your result set appear as a comma-separated list, a space-separated list, or whatever separator you choose to use.
While it’s true that you could achieve this same effect prior to SQL Server 2017, it was a bit fiddly.
Transact-SQL now has the STRING_AGG() function, which concatenates the values of string expressions and places separator values between them. This works in much the same way to MySQL’s GROUP_CONCAT() function.
This article provides examples that demonstrate the T-SQL STRING_AGG() function.
Sample Data
First, here’s some sample data.
SELECT TaskId, TaskName FROM Tasks;
Result:
TaskId TaskName ------ ------------ 1 Feed cats 2 Water dog 3 Feed garden 4 Paint carpet 5 Clean roof 6 Feed cats
Example – Comma Separated List
So we could take the above data, and use the STRING_AGG() function to list all the task names in one big comma separated list.
Like this:
SELECT STRING_AGG(TaskName, ', ') FROM Tasks;
Result:
Feed cats, Water dog, Feed garden, Paint carpet, Clean roof, Feed cats
Of course, it doesn’t necessarily need to be separated by a comma. It can be separated by any expression of NVARCHAR or VARCHAR type, and it can be a literal or a variable.
Example – Combining Columns
We could also use the CONCAT() function to combine two fields together, separated by their own separator.
Example:
SELECT STRING_AGG(CONCAT(TaskId, ') ', TaskName), ' ') FROM Tasks;
Result:
1) Feed cats 2) Water dog 3) Feed garden 4) Paint carpet 5) Clean roof 6) Feed cats
Example – Null Values
If your result set contains null values, those values are ignored and a corresponding separator is not added.
If this isn’t suitable, you can provide a value for null values by using the ISNULL() function and passing in the value you’d like to use whenever a null value is encountered. Doing this ensures that you still get a result when a row contains a null value.
For example, consider the following query and result set:
SELECT TaskCode FROM Tasks;
Result:
TaskCode -------- cat123 null null pnt456 rof789 null
We can see that there are three null values within the result set.
If we run this through the STRING_AGG() function, we get this:
SELECT STRING_AGG(TaskCode, ', ') FROM Tasks;
Result:
cat123, pnt456, rof789
However, if we use the ISNULL() function to provide a placeholder for any null values, we get this:
SELECT STRING_AGG(ISNULL(TaskCode, 'N/A'), ', ') FROM Tasks;
Result:
cat123, N/A, N/A, pnt456, rof789, N/A
Example – Grouped Results
You can also use the STRING_AGG() function when grouping your result set. For example, you might want a list of albums grouped by artist.
To demonstrate this, imagine a database with two tables; Artists and Albums. There is a one to many relationship between these tables. For every artist, there could be many albums.
So a regular query joining both tables might look something like this:
USE Music; SELECT ar.ArtistName, al.AlbumName FROM Artists ar INNER JOIN Albums al ON ar.ArtistId = al.ArtistId;
Result:
ArtistName AlbumName ------------------------- ------------------------ Iron Maiden Powerslave AC/DC Powerage Jim Reeves Singing Down the Lane Devin Townsend Ziltoid the Omniscient Devin Townsend Casualties of Cool Devin Townsend Epicloud Iron Maiden Somewhere in Time Iron Maiden Piece of Mind Iron Maiden Killers Iron Maiden No Prayer for the Dying The Script No Sound Without Silence Buddy Rich Big Swing Face Michael Learns to Rock Blue Night Michael Learns to Rock Eternity Michael Learns to Rock Scandinavia Tom Jones Long Lost Suitcase Tom Jones Praise and Blame Tom Jones Along Came Jones Allan Holdsworth All Night Wrong Allan Holdsworth The Sixteen Men of Tain
As you can see, if an artist has more than one album, the artist’s name is listed multiple times – once for each album.
But we can use STRING_AGG() to change this so that we list each artist only once, followed by a comma-separated list of albums they’ve released:
USE Music; SELECT ar.ArtistName, STRING_AGG(al.AlbumName, ', ') FROM Artists ar INNER JOIN Albums al ON ar.ArtistId = al.ArtistId GROUP BY ArtistName;
Result:
ArtistName ------------------------- ------------------------------------------------------------------------------ AC/DC Powerage Allan Holdsworth All Night Wrong, The Sixteen Men of Tain Buddy Rich Big Swing Face Devin Townsend Ziltoid the Omniscient, Casualties of Cool, Epicloud Iron Maiden Powerslave, Somewhere in Time, Piece of Mind, Killers, No Prayer for the Dying Jim Reeves Singing Down the Lane Michael Learns to Rock Blue Night, Eternity, Scandinavia The Script No Sound Without Silence Tom Jones Long Lost Suitcase, Praise and Blame, Along Came Jones
Example – Ordering the Results
You can use an order clause to order the results within the concatenated group. This is done with the WITHIN GROUP clause. When using this clause, you specify the order with ORDER BY followed by either ASC (for ascending) or DESC (for descending).
Example:
USE Music; SELECT ar.ArtistName, STRING_AGG(al.AlbumName, ', ') WITHIN GROUP (ORDER BY al.AlbumName DESC) FROM Artists ar INNER JOIN Albums al ON ar.ArtistId = al.ArtistId GROUP BY ArtistName;
Result:
ArtistName ------------------------- ------------------------------------------------------------------------------ AC/DC Powerage Allan Holdsworth The Sixteen Men of Tain, All Night Wrong Buddy Rich Big Swing Face Devin Townsend Ziltoid the Omniscient, Epicloud, Casualties of Cool Iron Maiden Somewhere in Time, Powerslave, Piece of Mind, No Prayer for the Dying, Killers Jim Reeves Singing Down the Lane Michael Learns to Rock Scandinavia, Eternity, Blue Night The Script No Sound Without Silence Tom Jones Praise and Blame, Long Lost Suitcase, Along Came Jones