GROUP_CONCAT() With InterSystems Caché

Answers

Looks like, it works exactly as LIST function in Caché

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.

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.