Is there any good way to build a comma-separated list in SQL Server?
In Firebird, there’s an aggregate called
List() that turns multiple results into a comma-separated string.
This function does not appear to exist in SQL Server. Is there any equivalent to it that doesn’t involve a big, long, ugly, slow workaround using
for xml or building your own as a CLR UDF? (Yes, I know about those methods. Looking for something I might not be aware of.)
One Solution collect form web for “Is there any good way to build a comma-separated list in SQL Server?”
No, those are the workarounds you will have to use. We have been asking for a
GROUP_CONCAT equivalent since 2006:
…but the item has only received 13 up-votes (and one down-vote). So I don’t exactly blame Microsoft for focusing on other enhancements to the product in order to drive sales and customer satisfaction. Not a lot of people have complained (at least not directly), and
GROUP_CONCAT is not something the marketing people can make look sexy on a slide deck or in a commercial – developer productivity is not exactly what sells licenses. (On the other hand, I’d like to meet the 14+ people who managed to get silly things like
So, if you think this functionality is important to have, then please vote and, more importantly, comment on how this will improve your productivity / bottom line:
FWIW, the XML version is ugly, but it is not hard to master. I’ve used it in dozens of answers here on SO and I don’t even have to go look it up anymore. I also don’t find it to be terribly slow. Why do you think something Microsoft puts in natively will be any faster? It will have to do essentially the same thing.