Grouping data into a comma-separated string

[Bowenaus]

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

1	mukesh	gupta	2	Leadership
3	neelam	sharma	3	Hard-working
1	mukesh	gupta	2	Responsible
2	devesh	tyagi	2	Punctuality
3	neelam	sharma	3	Self-motivated
1	mukesh	gupta	2	Quick-learner
4	keshav	singhal	3	Listening
2	devesh	tyagi	2	Quick-learner
5	tanya	jain	1	Hard-working
4	keshav	singhal	3	Critical thinking
5	tanya	jain	1	Goal-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

   1	mukesh	gupta	2	Leadership, Resposible, Quick-learner
   2	devesh	tyagi	2	Punctuality, Quick-learner
   3	neelam	sharma	3	Hard-working, Self-motivated
   4	keshav	singhal	3	Listening, Critical thinking
   5	tanya	jain	1	Hard-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%2....

Thanks!

Austin B.

[DavidKangni · Accepted solution]

Hi Austin,

You can try Adding additional SQL functions 

Thanks,

David

[Bowenaus]

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 

[reddyx0133]

did you find the solution for this ?

[Bowenaus]

Hi @reddyx0133 - instead of the comma-separated string, we were able to find an approach that iterated through all of the records belonging to a user in a data table & create a list of their specific associates that were active on the website in the past 24 hours &send in an email to the user.

See:

https://experienceleaguecommunities.adobe.com/t5/adobe-campaign-classic-questions/loop-through-linke...

[reddyx0133]

@DavidKangni Can you please provide the any other solution other than adding additional SQL functions.

[Parth1]

How to achieve this in adobe campaign standard?

[fofana]

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.

[talenttitan]

To group data into a comma-separated string in SQL, you can use the GROUP_CONCAT function in MySQL or STRING_AGG in PostgreSQL and SQL Server.

1. MySQL

Use GROUP_CONCAT to aggregate values as a comma-separated string:

sql

SELECT column1, GROUP_CONCAT(column2) AS comma_separated_values

FROM your_table GROUP BY column1;

2. PostgreSQL

Use STRING_AGG for a similar result:

sql

SELECT column1, STRING_AGG(column2, ', ') AS comma_separated_values

FROM your_table GROUP BY column1;

3. SQL Server

In SQL Server (2017+), you can also use STRING_AGG:

sql

SELECT column1, STRING_AGG(column2, ', ') AS comma_separated_values

FROM your_table GROUP BY column1;

For older versions of SQL Server, use FOR XML PATH:

sql

SELECT column1, STUFF((SELECT ', ' + column2 FROM your_table AS t2 WHERE t2.column1 = t1.column1 FOR XML PATH('')), 1, 2, '') AS comma_separated_values FROM your_table AS t1 GROUP BY column1;

Replace column1, column2, and your_table with your actual column names and table name.
Source: https://talenttitan.com/candidates/interview-preparation/all-skills/