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:

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

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

The output what i want to achieve is the following:

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.

ON m.orderid = j.Order_ID
  • Hi, two questions: 1) What is the Primary Key in table three? 2) What is the usecase for putting the ProductIds together? is it something like "displaying them as a list in an email"? I am asking because mashing several ProductIds together in one field is not something you'd want to commonly do. It would be better to have them in separate rows, which you can then iterate over to achieve your (presumed) usecase. Jul 17, 2020 at 10:12
  • Hi, thank you for your reply. The primary key in table three are USERID and ORDERID. I want to store only three product ID's, so SELECT TOP 3 will be the case in this story. It can also be seperated, because I will highlight them in an e-mail. But both I cannot get working :(
    – kvnwgnr
    Jul 17, 2020 at 10:27
  • OK I understand. Let me work up an answer. Jul 17, 2020 at 10:29

1 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:


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.


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)))



this should show:

  • Many thanks! Useful information, so thank you.
    – kvnwgnr
    Jul 17, 2020 at 13:06

You must log in to answer this question.

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