SQL: deduplicate multiple rows by field 1 while keeping multiple row values for field 2 [closed]

Question

I'm trying to deduplicate the following dataset so it can be used in a MC journey (Customer is currently showing multiple rows based on Contact ID). I want it so that there is only 1 row for each customer BUT the data from 'Asset_name" is not lost for each customer. So adding extra columns if the customer has multiple assets.

What is the best way to achieve this?

Thanks!

Answer 1

agree with Lukas; for most Database applications you would likely want to have it separated.

Still, in special scenarios I've had valid applications for something like this once in a while;

If you can live without creating separate rows for each asset_name, then this might help:

Put your asset_names into one field as a comma-separated list, and use that in emails using AMPScript's BuildRowSetFromString() function to loop through: https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/buildrowsetfromstring.html

==

Someone solved that approach here: https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv

Which is basically:

• select + subselect on the same DE - in my example it'll be textXMLPath, don't get confused by two the aliases de1 / de2, they're just there for the subselect to work.
• concat "," + each asset_name
• use FOR XML PATH to allow an OrderBy on Id, and place it in an xml node (we don't need the xml node)
• we now have a virtual node like <text>,a,b</text>
• for the created xml Node <text></text> - replace both start and end part of the node with nothing ('')
• we now have an virtual list like ,a,b
• Use substring to remove the spurious comma at the start of the list

input

sql - note it doesn't work in query studio (query isn't accepted) - use it in automation studio where it is valid. Note that I am assuming a maxlength of 1000 for "asset_name" in the result.

SELECT 
DISTINCT id, 
SUBSTRING(
        Replace(Replace((
            SELECT ','+de1.asset_name AS text
            FROM testXmlPath de1
            WHERE de1.id = de2.id
            ORDER BY de1.id
            FOR XML PATH ('')
        ),'<text>',''),'</text>',''), 2, 1000) asset_name    
FROM testXmlPath de2

output

Answer 2

I will recommend splitting this into two separate data extensions. One with unique contactIDs, being a sendable one. Another one with multiple rows per contactID (basically the one you have now). You will have a hard time estimating the number of columns to add, if you follow your initial approach, unless there is a defined max number of assets per each contactID.

Using two data extensions will allow you to inject contacts into the journey, while using Ampscript lookupRows function to populate your email with all relevant assets.