2

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.)

Mason Wheeler
  • 82,511
  • 50
  • 270
  • 477
  • 2
    There is no aggregate function that does this, you have to use the two options that you mentioned. – Taryn Apr 05 '13 at 18:49
  • 4
    Nope. See [Concatenating Row Values in Transact-SQL](https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/) – Martin Smith Apr 05 '13 at 18:51
  • 2
    @CadeRoux I don't know about the dupe. It doesn't have much else except a link-only answer that points to the article Martin already linked to... this question is asking for built-in functionality that doesn't require all of those workarounds, or a better workaround, neither of which exists. No answer to this question should have any of the code that's in the linked article. – Aaron Bertrand Apr 05 '13 at 19:03
  • 1
    @AaronBertrand I can delete the comment but not the vote. Has this question really not been answered here before? – Cade Roux Apr 05 '13 at 19:27
  • 1
    @CadeRoux no worries. I don't know that this specific question has been answered. All of the duplicates I could find actually have accepted answers with one of the workarounds the OP doesn't want. – Aaron Bertrand Apr 05 '13 at 19:29

1 Answers1

7

No, those are the workarounds you will have to use. We have been asking for a GROUP_CONCAT equivalent since 2006:

http://connect.microsoft.com/SQLServer/feedback/details/247118/sql-needs-version-of-mysql-group-concat-function

...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 EOMONTH and IIF implemented.)

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • "Developer productivity is not exactly what sells licenses." You know, that's probably the most screwed-up thing I've heard on here in a long time. This is a tool that's *useless to everyone except developers.* Why should anything but developer productivity sell licenses? What other factors matter **at all?** – Mason Wheeler Apr 05 '13 at 19:38
  • 2
    @MasonWheeler If those questions are meant to Aaron, then I think that you may be misdirecting them. All he is saying is that since the connect item for this functionality hasn't been massively supported, then it's hard to really blame Microsoft for not implementig them (and the "developer productivity is not exactly what sells licenses" is the reason why Microsoft hasn't implemented them). So, instead of asking those questions to the guy who pointed you to the links that can support the actual implementation of this features, you should go ask Microsoft and upvote those connect items – Lamak Apr 05 '13 at 19:51
  • @Mason surely you're not suggesting that SQL Server is only sold to developers for developers? Microsoft doesn't market to developers. They market to CIOs, CTOs, etc. Some of whom may be concerned about developer productivity, but most if not all are going to be more concerned about - for example - HA/DR features that save them money or prevent them from losing money. The developer's salary has already been paid. And like Lamak said, this is not a decision that *I* have made, so don't kill the messenger. Would it be nice to have this feature? Sure. Is it necessary? No. We have workarounds. – Aaron Bertrand Apr 05 '13 at 20:09