Return a list of related records in SQL Server 2017

Posted At : February 4, 2025 11:00 AM | Posted By : Steve
Related Categories: SQL

I absolutely love SQL (especially SQL Server) and I love how it continually gets better.

A little over four years ago, I wrong an entry on how to return a comma-delimited list of records in SQL Server. It turns out, that it is now much easier to do.

SQL Server 2017 introduced the STRING_AGG function. For those of you doing math, it would have actually been available when I wrote the previous entry, but we'll just ignore that and I'll use the same example.

For example, if you have users that can work in multiple regions and you need a list of which regions they work in.

SELECT   UserID,
         (
            SELECT   STRING_AGG(RegionID,',')
            FROM   userregions
            WHERE   userregions.UserID = users.UserID
         ) AS Regions
FROM   users
WHERE   1= 1

You will get back recordset of all of your users and each row will have a comma-delimited list of regions for that user. It will work for any subquery that returns a single varchar field.

That's really all you need.

What's great about this is that (1) the syntax is much simpler that the STUFF/XML method, but it is also much more flexible.

Whereas the STUFF/XML syntax really needs to be used in a subquery, STRING_AGG can be used just like any other aggregate function.

SELECT
         Max(u.UserID) AS UserID,
         STRING_AGG(u2r.RegionID,',') AS Regions
FROM      users u
JOIN      userregions u2r
   ON      u.UserID = u2r.UserID
WHERE      1= 1
GROUP BY   u.UserID

This has the same result as the previous query, but using a GROUP BY instead of a subquery.

The advantage of the subquery is that you can use an ORDER BY clause, but that can actually be done in the GROUP BY syntax as well.

SELECT
         Max(u.UserID) AS UserID,
         STRING_AGG(u2r.RegionID,',') WITHIN GROUP (ORDER BY u2r.RegionIDDESC) AS Regions
FROM      users u
JOIN      userregions u2r
   ON      u.UserID = u2r.UserID
WHERE      1= 1
GROUP BY   u.UserID

If, for some reason, you aren't on SQL Server 2017 or later then the STUFF/XML solution is the one to take. If you have STRING_AGG available, however, it is a much nicer solution.

Enjoy and let me know if you have any questions!