Get a one to many database output in one column in a one to one database Salesforce Marketing Cloud

Question

We have a one to many table where we store all our transactions and a table where we store all the seperate items they bought. So it looks like:

|   **TABLE ONE**  |      |   **TABLE TWO**      |
|   ORDERID        |      |   ORDERID            |
|   USERID         |      |   PRODUCTID          |

For example we have this data in the tables:

TABLE ONE
ORDERID   |  7  |  8  |  9  |  10  |
USERID    |  1  |  2  |  3  |  4   |

TABLE TWO
ORDERID   |  7  |  7  |  8  |  9  |  9  |  10  |
PRODUCTID |  5  |  6  |  5  |  5  |  6  |   6  |

The output what i want to achieve is the following:

TABLE THREE
USERID    |   1   |  2  |   3   |   4  |
ORDERID   |   7   |  8  |   9   |  10  |
PRODUCTID |  5|6  |  5  |  5|6  |  6   |

With the code below it will pick up only the first productID, where the wish is to add all productID's in one column in the new table.

SELECT
j.Product_ID,
j.Order_ID,
m.userid,
m.orderid
FROM [TABLE ONE] m
INNER JOIN [TABLE TWO] j
ON m.orderid = j.Order_ID

Answer 1

Set up table three like this:

Make all three fields the primary key. Alternatively, make no field the primary key and create a rowKey instead. But let's focus on option #1, which will likely be more performant.

Consider table three "products by order" . Intuitively, here the combination of Person, Order and Product defines a unique row, not just the combination of Person and Order.

With this change, your existing query should list multiple rows for each user and order in table three.

You first user's products should be represented like this:

UserId,OrderId,ProductId
1,7,5
1,7,6

Do not bother combining the Ids into one field. You can work on separate rows as well.

In your email, make use of the LookupRows function to get multiple rows to work in an email. This will give you all rows for the user in question as a rowset. Possibly you'll have to also limit the lookup by OrderId.

Maybe you'll have to use lookupOrderedRows to sort by the latest order, limit the results to three or similar.

https://developer.salesforce.com/docs/atlas.en-us.mc-programmatic-content.meta/mc-programmatic-content/ssjs_platformDataExtensionLookupOrderedRows.htm

Just understand that without further limitation, all products from several orders will show for the user. This is the case in the code example below.

With your rowset, iterate through each line in a for loop and print the ProductId plus a ",". Afterwards, remove the last comma.

%%[
SET @productsByOrderAsRowset = LookupRows('products by order','subscriberKey',_subscriberkey) /* you might want to add 'orderId' to the lookup here)

SET @rowCount = rowcount(@productsByOrderAsRowset)

SET @resultList = ""
IF @rowCount > 0 THEN
  FOR @i = 1 TO @rowCount DO

    SET @row = row(@productsByOrderAsRowset,@i) /*get row based on loop counter */
    SET @productId = field(@row,1)
    SET @resultList = Concat(@resultList,@productId, ",")
  NEXT @i

/* trim last comma from the list of products*/
SET @length = length(@resultList)
SET @resultList = Substring(@resultList,1,subtract(@length,1)))

]%%

%%=v(@resultList)=%%
%%[
ENDIF
]%%

this should show:

5,6