If you’re on a new-ish version of SQL Server, using STRING_AGG is your best bet for this. Please use it instead.
Unlike STRING_SPLIT, it’s not compatibility-walled. You don’t need to be on level 130+ to use it.
If you’re on an earlier version, your most reliable bet is using XML. Using the local variable method can have quite unexpected results at times. I’ve seen it go from returning a full list of values to only returning the last value more times than I can count.
Let’s look at the XML version, though. Just in case you’re not on SQL Server 2017.
The purpose of these queries is to show you hot to remove XML elements, and handle XML control characters like &, <, >, etc. All of these results return a single row, just to keep the examples simple.
SELECT x = ( SELECT b.Name FROM dbo.Badges AS b WHERE b.Id = 100564 FOR XML PATH('') );
This will return two things we don’t want:
- XML elements
- An ampersand turned into &
To get rid of the XML elements, we can alias the inner results as
SELECT x = ( SELECT [text()] = b.Name FROM dbo.Badges AS b WHERE b.Id = 100564 FOR XML PATH('') );
That will give us this back, still with the ampersand all mangled up.
To fix that, we need to add a little bit to the XML-ing:
SELECT x = ( SELECT [text()] = b.Name FROM dbo.Badges AS b WHERE b.Id = 100564 FOR XML PATH(''), TYPE ).value ( './text()', 'nvarchar(max)' );
First, we need to add
TYPE to the
XML PATH syntax. That’ll give us an XML typed return type to use the
.value method on.
From there, we can grab the text element, and give it a data type. You don’t specifically need to get
./text() though, but I tend to use it because I’ve seen some very weird performance issues when using less verbose expressions like
. or just
Here are the plan differences, which are negligible for a single row.
Of course, local factors may require deviating from what generally works best.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.