October 23, 2024 at 5:35 pm
Comments posted to this topic are about the item Concatenating Multiple Row Values into a Single Comma-Separated List
October 23, 2024 at 8:49 pm
I've almost completely abandoned this approach since the introduction of STRING_AGG(), because it's just so much simpler to implement (and therefore easier to understand). You need to offer a compelling reason to use FOR XML PATH over STRING_AGG(), and you haven't even tried to do so.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 23, 2024 at 9:04 pm
In case if you have missed, I did mention "You can also use STRING_AGG (SQL Server 2017+) to achieve same results."
October 24, 2024 at 2:11 pm
In case if you have missed, I did mention "You can also use STRING_AGG (SQL Server 2017+) to achieve same results."
No, I caught that. You stated that there is an alternative. PERIOD. You made no effort to compare the two alternatives. STRING_AGG() has much simpler syntax, so you need to prove that FOR XML PATH performs much better or has some functionality that isn't available with STRING_AGG() that makes it worthwhile to use the more complex approach. You've provided the HOW, but not the WHY.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 24, 2024 at 3:26 pm
Thanks for the critical feedback! I appreciate your point, but the intent of my script wasn't to compare performance between the two methods. It was focused on providing a solution for legacy systems where STRING_AGG() isn't available, and FOR XML PATH offers a way to perform string concatenation.
That said, I shall write a separate post comparing the two in terms of performance. However, it's worth noting that FOR XML PATH has some advantages in specific cases, such as handling complex string concatenation, concatenating multiple data points, and managing special characters more effectively than STRING_AGG(). So, in some scenarios, it's important to balance performance with the desired output structure.
October 24, 2024 at 4:52 pm
Thanks for the critical feedback! I appreciate your point, but the intent of my script wasn't to compare performance between the two methods. It was focused on providing a solution for legacy systems where STRING_AGG() isn't available, and FOR XML PATH offers a way to perform string concatenation.
Yet, you failed to express this intent anywhere in your article. I also have to question why you are focused on providing solutions for versions of SQL Server that are now out of service instead of encouraging people to upgrade to a version that is current.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 11, 2024 at 11:13 am
Sukhdevsinh Dhummad wrote:Thanks for the critical feedback! I appreciate your point, but the intent of my script wasn't to compare performance between the two methods. It was focused on providing a solution for legacy systems where STRING_AGG() isn't available, and FOR XML PATH offers a way to perform string concatenation.
Yet, you failed to express this intent anywhere in your article. I also have to question why you are focused on providing solutions for versions of SQL Server that are now out of service instead of encouraging people to upgrade to a version that is current.
I feel like you're a little too prickly this fine morning.
Many organisations don't upgrade to a newer version of SQL Server until it's absolutely necessary. This necessity can take many forms, but for many this will be when the SQL Server version is nearing the end of its Extended Support, which for Microsoft SQL Server 2016 will not be before mid July 2026.
https://www.mssqltips.com/sqlservertutorial/9217/sql-server-release-dates-and-lifecycle/
You are of course free to rant about the recalcitrant nature of organisations that don't quickly move to a newer version when it becomes available. However, the fact remain that SQL Server 2016 (still on Extended Support) doesn't support the STRING_AGG() function.
November 11, 2024 at 1:25 pm
Thank you @Sukhdevsinh for this writeup!
😎
Now my question is; Why would you like to concatenate multiple values into a single value? I consider that as an antipattern that should be avoided as it creates many downstream problems. Think of it this way, if a sparse column table cannot handle the multiple values, there is most likely a problem with the design.
November 12, 2024 at 9:09 pm
Thank you @Sukhdevsinh for this writeup! 😎 Now my question is; Why would you like to concatenate multiple values into a single value? I consider that as an antipattern that should be avoided as it creates many downstream problems. Think of it this way, if a sparse column table cannot handle the multiple values, there is most likely a problem with the design.
Thank you for the appreciation!
I completely agree that storing data as comma-separated values within a database column is generally an antipattern and can lead to downstream issues. The script I shared is intended for scenarios where concatenating values is necessary for presentation purposes, such as displaying information in a UI grid or report.
In my specific case, there is a requirement to show a list of values in the UI as pipe-separated, e.g., "Item 1 | Item 2 | Item 3". It's purely for display convenience, not for data storage. Also, we're currently working with SQL Server 2016, which limits our options a bit.
November 13, 2024 at 4:10 pm
However, it's worth noting that FOR XML PATH has some advantages in specific cases, such as handling complex string concatenation, concatenating multiple data points, and managing special characters more effectively than STRING_AGG().
This might be a topic for another article. I'd certainly be interested in seeing some examples.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply