GROUP_CONCAT() With InterSystems Caché
Hello,
Is there some way to reproduce the SQL GROUP_CONCAT(http://sql.sh/fonctions/group_concat) with the Caché DB ?
Thanks
Sébastien
Comments
Looks like, it works exactly as LIST function in Caché
Exactly that :)
Thanks a lot.
Strange but with this function the perfermance are really bad :(
0.04s without and 13s with it ...
If you want to investigate the performance of the query you can always open a WRC Case (wrc.intersystems.com :-D).
Otherwise, send out your class definition and query plan and I can take a cursory look at it.
I tried with an other table with just 2 properties ( but more than 1million records too) and that's the same problem.
see:
Looking at that documentation, one difference between LIST and GROUP_CONCAT is that GROUP_CONCAT lets you specify the separator, while LIST always uses a comma.
If you wanted to use a different separator, and your data will never contain commas, then it's as easy as (for example):
select home_city as "City", count(*) as "Count", REPLACE(LIST(Name),',',' ') as "Names" from sample.person group by home_city
If "your data will never contain commas" is a bad assumption (as it is in the case of Name in Sample.Person), the solution is to use %DLIST and $ListToString.
select home_city as "City", count(*) as "Count", $ListToString(%DLIST(Name),' ') as "Names" from sample.person group by home_city
%DLIST builds a $ListBuild list, and $ListToString joins the list elements with the specified separator.
%DLIST is useful in other cases too - for example, if your data might contain commas and you want to iterate over the aggregated data after running dynamic SQL.