Marketing Cloud - How to group DE rows with repeated values

Question

I have a DE (DataExtension) for members, and another for members' children, which are also members but linked to the parent in question.

I would like to be able to group up sends, so I don't overburden the parent with, for example 3 emails if they register their 3 kids at the same time. Both DE's are linked by the parent's Id, the column used later for the send logic.

One of the options I though of is achieving something like this:

I was thinking of using the STUFF function, but I don't see a way to make it work.

Any idea how to achieve this?

Answer 1

I actually wrote a blog post a while back about pretty much this exact thing. Reference here for a ton of more details on the whole process.

You were right to go with Stuff, but I think the part you were missing was the FOR XML PATH part.

Example of solution:

SELECT a.id,
emailStr = STUFF(
  (
    SELECT ',' + b.email
    FROM [myDE] b
    WHERE a.id = b.id
    FOR XML PATH('')
  ), 1, 1, '')
FROM [myDE]
GROUP BY ID 

In this case it takes the email field from myDE and makes it a delimited field that is grouped together by the id field.

Answer 2

We are unable to completely understand your requirement! Based on a similar project, I have tried to illustrate your requirement. Hope this helps you to implement. [If NOT at least it might help some others trying to implement similar one!.

• Hoping your NOT Triggering emails immediately after registration.