On 15/08/2017 17:12, Bob Friesenhahn wrote:
I am surprised by this behavior of group_concat():
The implementation of SQLite "group_concat" (a very powerful but often
overlooked function) provides some but not all the features found in
other DBMS (MySQL "group_concat" and PostgreSQL "string_agg" for example):
- the values to concatenate (SQL string expression),
- the separator, default (comma) or user-defined (SQL string expression),
- a uniqueness contraint on the values of each group,
- the order of the values.
As far as I understand the specs, SQLlite provides the first three, but
with a frustrating constraint: you must choose between the uniqueness
and the user-defined separator but you cannot have both.
The "order by" is badly needed, so, programmers tend to use the
workaround suggested in this thread: sorting the values in a "from"
subquery. This is intuitive and works fine in the current version but,
as said in the documentation, this order is not guaranteed to propagate
to the concatenated list .
The uniqueness constraint can be enforced in a "from" subquery and the
user-defined separator can be merged with the values to concatenate,
followed by some cleaning. As I saw in various forums, it seems possible
to force the ordering with a CTE (I have not checked). However this
makes the final expression horribly complicated.
I personally have implemented (in Python) a UDF aggregate function that
simulates the full group_concat version. But it would be nice to
include a full-fledged function (whatever the syntax) in a future SQLite
version.
Why not in the Christmast version for example?
Best regards
Jean-Luc Hainaut
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
