Have you ever run across a one to many relationship that you need to have listed fully in a single field? You are not in the minority. There are tons of places, especially is something like an MDM that requires this type of data storage.

The issue is, how do you do this inside of SFMC? Inside the UI, there is nothing outside of attribute groups or contact models that will let you match the data 1:Many – but this does not provide a direct reference and can only be utilized in specific areas. Sure, you could run a scripting solution through AMPScript or SSJS, but that is a laborious and slow process. If you have millions of records with millions of matches, you could be looking at days of processing for the script to fully run. What about SQL?

SQL Solution

So, there are quite a few native SQL solutions that are highly performant. The issue is most of them require you to use functions and variable capabilities which are not possible in SFMC. SFMC version of T-SQL is notoriously limited (see here for some of the documented limitations) and most of the cool or efficient processes that SQL offer are not available in SFMC Queries. All hope is not lost though! There is a solution that fits inside of a SFMC environment.* You utilize FOR XML PATH combined with STUFF to create your delimited list!

*One note to keep in mind is that although this will work in a Query Activity in Automation Studio, Query Studio will incorrectly toss an error and not allow you to use this method.

What the heck is a STUFF()?

Ok, but really, what the heck is STUFF? Is there a THINGS() function too? I am sure Rick Grimes would love that. Silliness aside, STUFF is a really cool function inside SQL. The STUFF function is actually a combination of other things inside a single function. It will delete a designated part of a string and then insert another string into the start position index of the deletion. Super cool! Now, unlike many other super cool and fun things that SQL has to offer, this actually works in SFMC! Score! Now after we go over FOR XML PATH, I will get into details about how this is relevant to our needs.

Sample of STUFF():

SELECT STUFF('SFMC SQL Queries!', 17, 1, ' can use STUFF!');

(Everything I Do) I Do It FOR XML PATH()

FOR XML PATH() is another super awesome function/capability of SQL. PATH mode of FOR XML basically generates and outputs element based XML from the results of a SELECT query. Whoa! Talk about a useful capability! There is a ton of other awesome things we could do with FOR XML, but to keep things on topic, we are going to concentrate on FOR XML PATH() focused on creating a delimited string.

So, before we go further, I do want to point out that about 99% of the work to create this delimited string is done by FOR XML PATH, STUFF just offers the finishing touch. To help understanding of how the heck we get there, I have broken things down to show what each addition to the generic calling of this capability does.

Default usage (nothing specificed)

This query:

SELECT id, email
FROM [myDE]
FOR XML PATH

Would return:

<row>
    <id>1</id>
    <email>[email protected]</email>
</row>
<row>
    <id>2</id>
    <email>[email protected]</email>
</row>
<row>
    <id>3</id>
    <email>[email protected]</email>
</row>
<row>
    <id>4</id>
    <email>[email protected]</email>
</row>

Neat! Right? But…how is this helpful for us? Glad you asked! So by default the output of FOR XML PATH is element-centric, where each row is separated out into a row element. Well, having these row elements can be a bit of a problem when we are trying to iterate through it to add our values together into a delimited string. To remove the row elements, we just add in a ‘tag’ to the function, the wrapping element is no longer produced.

Removing the wrapping elements

This query:

SELECT id, email
FROM [myDE]
FOR XML PATH('')

Would return:

<id>1</id>
<email>[email protected]</email>
<id>2</id>
<email>[email protected]</email>
<id>3</id>
<email>[email protected]</email>
<id>4</id>
<email>[email protected]</email>

Success! That is one part down, now what? So for our example, we only want a list of the email addresses, so id is not needed. To concentrate on just the column that we want in our string, we would remove id in the select statement. Ok this would then return only the element name/value pairs of email. But, we don’t need the element wrapper with the name, only the value. How do we do that? By adding in a comma prepend to the column we are returning in our SELECT statement, the server no longer knows what name to wrap it in so it just disappears and only value is presented.

Creating the first draft of the delimited string

This query:

SELECT ',' + email 
FROM [myDE]
FOR XML PATH('')

Would return:

,[email protected],[email protected],[email protected],[email protected]

Progress! But this is still not quite right and its just a single output. We need to be able to assign this to each of the ids inside the source Data Extension. By using a subquery to set the value, you can get this delimited value for each row. You can also add in conditions (WHERE x = y, etc) to limit the output delimited string and make it unique per row! Then to top it off, you can use a GROUP BY in the top level query to ensure it is returning unique records only.

Delimited Strings…Well Almost

This query:

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

Would return:

ID  |   EmailStr
1   |   ,[email protected],[email protected],[email protected]
2   |   ,[email protected],[email protected],[email protected],[email protected]
3   |   ,[email protected],[email protected]
4   |   ,[email protected]

So close now! Just have that darn pesky opening comma prepending your delimited string. What to do…What to do? This is where STUFF() comes in to save the day! By setting the STUFF function around the FOR XML PATH subquery, you can take that result and replace that initial comma and replace it with an empty string. Then BLAMO you got your delimited string all nice and pretty!

We Did It!

So you would just do this:

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

Which would produce an output like:

ID  |   EmailStr
1   |   [email protected],[email protected],[email protected]
2   |   [email protected],[email protected],[email protected],[email protected]
3   |   [email protected],[email protected]
4   |   [email protected]

And there you have it. With FOR XML and STUFF, you can now have unique delimited strings of all the corresponding values inside a single column. I do want to warn that although this is fast, it can add a lot of processing with volume and timeout restrictions need to be considered. Please make sure to load test your queries before implementing into production!