array_agg postgresql in AC | Community
Skip to main content
szymons55769873
szymons55769873
Level 2
October 22, 2019
Solved

array_agg postgresql in AC

  • October 22, 2019
  • 1 reply
  • 1546 views

Hello

Is it possible to group (concat) certain string values in one column by common value in other column in AC Classic? I've tried with Group By but it's for numerical aggregates and there's a function in postgresql which does that (array_agg) and wondering if it's possible somehow to emulate it with activities? (Having in mind that with SQL Activity it's not possible to make outbound transition with select results)

Eg.

City             |  Country

------------------------------

Madrid          Spain

Barcelona     Spain

Paris               France

Nice               France

Output would be:

Madrid, Barcelona     Spain

Paris, Nice                  France

Kind regards

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by Jonathon_wodnicki

    Hi,

    You can probably do this with queryDef, defining the groupBy independently of the <select/> and enabling unknown sql functions/adding array_agg function. If not it's just a couple lines extra to spool the results into a map then update the temp schema with '{' + map[k].join(',') + '}'.

    Thanks,

    -Jon

    1 reply

    Jonathon_wodnicki
    Community Advisor
    Jonathon_wodnickiCommunity AdvisorAccepted solution
    Community Advisor
    October 22, 2019

    Hi,

    You can probably do this with queryDef, defining the groupBy independently of the <select/> and enabling unknown sql functions/adding array_agg function. If not it's just a couple lines extra to spool the results into a map then update the temp schema with '{' + map[k].join(',') + '}'.

    Thanks,

    -Jon

      Terms & ConditionsAccessibility statement

      Loading footer...