Hello again!
How many times you guys have had to create a list of values separated by commas? I’ve been in that situations for several times now, and I guess you have as well.
In the past, we had to go and and some weird way to do this parsing to XML, using the STUFF function and so on. That was before SQL Server 2017, where a new friend came to make our lives easier.
STRING_AGG()
This function is great, it will help you to concatenate values using the separator of your choice and the best thing is, it won’t add it to the end of the string. I’ll show you with an example.
For this part, I’m going to be using the WorldWideImporters database. There are several ways to use STRING_AGG, this is the simplest.
If you see in the result, we just generated a list of comma separated values. Very easy. Let’s see another example. There’s a catch using this function, I’ll show you.
We just had an error. It says:
STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation.
So, why did we have that error? What does that mean?
That error is simply because, the STRING_AGG function returns a certain data type depending on the data type we pass. Here’s the list of input and output data types:
| Input Data Type | Output Data Type |
|---|---|
| int, bigint, smallint, tinyint, numeric, float, real, bit, decimal, smallmoney, money, datetime, datetime2 | NVARCHAR(4000) |
| VARCHAR(1…8000) | VARCHAR(8000) |
| NVARCHAR(1…4000) | NVARCHAR(4000) |
| VARCHAR(MAX) | VARCHAR(MAX) |
| NVARCHAR(MAX) | NVARCHAR(MAX) |
HOW TO FIX IT?
The fix is really easy, and I guess you’ve already figured it out. Yes, the only thing we need to do in case we face this error message is to cast the value we are passing to the one suitable for our needs.
In my example, I had to cast the EmailAddress column to a NVARCHAR(MAX) and it worked wonderfully. We can now see the list of email addresses we needed for our spam marketing email list.
GROUPING AND SORTING
It doesn’t really matter much if you use the optional WITHIN GROUP clause of the function, unless you need to make 100% sure that your comma separated list is sorted.
Let’s see.
Here, we have the States and the Cities on a comma separated list, but if you take a closer look, it is not sorted. All the names are in the randomest order possible.
In order to have it sorted properly, you NEED to use the WITHIN GROUP(ORDER BY ) clause like in the query below.
There you go. We now see the list of cities by state sorted alphabetically.
In case you were wondering why we need the GROUP BY function there, the answer is right there in the STRING_AGG function. What I mean is, STRING_AGG is an aggregate function, that’s why we need the group in there.
Anyhoo. We have just learned a new way to create comma separated values lists (or the separator of your choice) in a very efficient and easy way thanks to our friend SQL Server 2017. In case you’re still in a previous version, you would still need to use the old way to do this.
You can find the code for this and previous posts in the link below:
https://github.com/learningsql/sql-code
Also, if you don’t have the WorldWideImporters database, you can get it here:
https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/wide-world-importers
I hope you guys liked the post. Don’t forget to like and share. See you soon.