I have a data extension with a customerID and 15 products assigned to them(screenshot attached)

enter image description here

Some of the 15 products are missing the value and I would like to write a query so if Product1 is empty to get the value of Product2 and so on.

i.e. if product1 product3 and product5 are empty to show the first 12 products with the value inherited from the next product and last 3 products to be empty in this case because there is 3 empty products.

Is this possible?


I'd suggest not trying to "fill in" the blank productIDs, but rather normalize the data into a more usable format. Like this:

|CustomerID|Product   |
|6180142400|100281586 |

It's much easier to lookup and iterate through data extension rows like this using AMPscript in an email than it is to have named columns, IMO.

Since SFMC doesn't support the normal pivot/unpivot functions, here's my go-to for unpivoting:

, c.field
, c.fieldValue
from [unpivot-test] t
cross apply (
  select 'field1', t.field1 union all
  select 'field2', t.field2 union all
  select 'field3', t.field3 union all
  select 'field4', t.field4 union all
  select 'field5', t.field5
) c (field, fieldValue)

You'll need a union all select for each productID column in your data extension.

  • Hi Adam, thanks for taking the time again. Unfortunately I need the columns as they stand for a product recommendation model that I've created. I will just use AMPscript. – Duicug Nov 5 '19 at 13:15
  • If that's the case, what I typically do (after pushing back that this architecture is not ideal) is build a pipe-delimited string of "display productIDs" and iterate through that for output. It's just a bunch of if-statements. I have another looping solution, but it has some constraints. – Adam Spriggs Nov 5 '19 at 15:40
  • The main reason why I wanted to do that from the data extension is the HTML file size. I have over 2300 lines and about 120kb in size which is not ideal as gmail is shrinking my email so if I add the logic to skip the empty values it gets too big. Do you have any solution for that Adam? Thanks a lot – Duicug Nov 6 '19 at 8:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.