cross server, standard SQL to produce a comma delimited list

Question

I've been looking but can't find anything. Is there a SQL standard compliant way to produce output like this TSQL? I would like a single query that would work just as well on sqlite, mysql, postgresql or sql server. Is something like that even possible?

Answer 1

As I understand your question you are looking for a cross-database way to aggregate values in a column into a comma separated list. As far as I know there is no standard aggregate to do this (and on PostgreSQL prior to some new aggregates added, I was writing my own aggregates for this).

The best you could do would be to select the list and process it in your application, but that's what happens when you have to write code portable across db's.

The second problem you will have is that while it is possible to add extensions to some of these db's to manage this problem it isn't possible on all of them.

So no, not possible. Your choices are: stick with one or two db's that support custom aggregates and shim that in, build an SP-based interface on one db, or just select the list and handle it in your application.