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?


enter image description here

  • How many is it? You could export the data and then fix them in Excel then upsert.
    – iLLin
    Feb 9, 2022 at 22:37
  • No, this is an ongoing data (daily automation) set for customers to return a product. They can have multiple products to return as you can see in the above data set. So that we can use JB i wanted to deduplicate without losing "Asset_name" data. Thanks! Feb 9, 2022 at 22:43
  • Deduplicating rows is a common thing. Have you attempted a query? If so, please update your question to include it. Feb 10, 2022 at 0:49
  • Hey Adam, i want to dedup but keep the data in "asset_name". for example the above data set to only have 4 unique rows but with added columns of "nokia modem" "other modem". Hope this makes sense Feb 10, 2022 at 0:57

2 Answers 2


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


enter image description here

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 ','+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


enter image description here


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.

Not the answer you're looking for? Browse other questions tagged .