Grouping data into a comma-separated string | Community
Skip to main content
B
Bowenaus
September 10, 2020
Solved

Grouping data into a comma-separated string

  • September 10, 2020
  • 2 replies
  • 6304 views

Hello,

 

We have a need for a workflow which is capable of replicating the Group_CONCAT() function in SQL.

 

All of the "Group By" options I've seen in Adobe Campaign are just capable of aggregating #s together but I can't find anything which can concatenate strings into a comma-separated string like this example:
 
 
EMP_ID FNAME LNAME DEPT_ID STRENGTH
1mukeshgupta2Leadership
3neelamsharma3Hard-working
1mukeshgupta2Responsible
2deveshtyagi2Punctuality
3neelamsharma3Self-motivated
1mukeshgupta2Quick-learner
4keshavsinghal3Listening
2deveshtyagi2Quick-learner
5tanyajain1Hard-working
4keshavsinghal3Critical thinking
5tanyajain1Goal-oriented

Queries:

  1. Using simple GROUP_CONCAT() function-
    SELECT emp_id, fname, lname, dept_id, 
GROUP_CONCAT ( strength ) as "strengths" 
FROM employee group by emp_id;

    Output:

    EMP_ID FNAME LNAME DEPT_ID STRENGTHS
    1mukeshgupta2Leadership, Resposible, Quick-learner
    2deveshtyagi2Punctuality, Quick-learner
    3neelamsharma3Hard-working, Self-motivated
    4keshavsinghal3Listening, Critical thinking
    5tanyajain1Hard-working, Goal-oriented
 

If anyone has any ideas on how to apply this logic using the targeting activities available in Adobe Campaign Classic, please let me know!

 

Source: https://www.geeksforgeeks.org/mysql-group_concat-function/#:~:text=The%20GROUP_CONCAT()%20function%20in,least%20one%20non%2DNULL%20value.

 

Thanks!

Austin B.

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 DavidKangni

Hi Austin,

 

You can try Adding additional SQL functions 

 

Thanks,

David

2 replies

DavidKangni
Community Advisor
DavidKangniCommunity AdvisorAccepted solution
Community Advisor
September 10, 2020

Hi Austin,

 

You can try Adding additional SQL functions 

 

Thanks,

David

David Kangni
    B
    BowenausAuthor
    October 13, 2020

    Thanks David. I was able to add a function groupConcat() but struggling to put this into practice by leveraging the function inside of the .js. Is there an easier way to iterate through & group the records together using one of the built-in jsapi functions? In the end, I just need to send a single email to Manager Id1 listing each of their associate emails in some way. I'm probably overcomplicating it. 

    Basically my table is something like this:
    Record Id      |      Manager Id   |     Associate Email
    1                    |              1           |       email1@adobe.com
    2                    |               1          |       email2@adobe.com
    3                    |               2          |       email3@adobe.com 

     

    I want to group by Manager Id to output this:
    Record Id      |      Manager Id   |     Associate Email
    1                    |              1           |       email1@adobe.com, email2@adobe.com
    3                    |               2          |       email3@adobe.com 

    R
    reddyx0133
    December 1, 2021

    did you find the solution for this ?

    F
    Adobe Employee
    fofanaAdobe Employee
    Adobe Employee
    January 19, 2021

    Hi, Please bear in mind that you have referenced this GROUP_CONCAT() function from MySQL DB Engine. But as you may know, most of Adobe Campaign solution are implemented with PostgreSQL DB engine. So you need to find the equivalent aggregate function depending of your instance DB Engine.

    Terms & ConditionsAccessibility statement

    Loading footer...