Output duplicate row data within an email

Question

Hopefully someone might be able to help me. I'm fairly new to MC and AMPScript . What I am trying to do is send out an email that includes the Person's Business ID. The issue we come up against is that if a person is associated to more than one business. I don’t want to send an individual email per business so is there a way to write a script to output all business IDs for a specific email address or subscriber key that will populate into the email? Our setup has duplicated data based per business. So if i have 5 business i will be in the data 5 times, what i need is a script that see's those 5 email address and merges the business ID into 1 email when it de dupes on a send.

Eg:

• Sub key = 12345 - email add= [email protected] - BusinessID = 12345
• Sub key = 12345 - email add= [email protected] - BusinessID = 12346
• Sub key = 12345 - email add= [email protected] - BusinessID = 12347
• Sub key = 12345 - email add= [email protected] - BusinessID = 12348
• Sub key = 12345 - email add= [email protected] - BusinessID = 12349

Email output would be Hi Business owner.

We are just updating our records as we currently have the following business IDs on file for you 12345, 12346, 12347, 12348, 12349

Your help would be much appreciated.

Here is the code used based on Adam's recommendations

%%[
var @rows, @row, @rowCount, @returnRows, @Subscriber Key, @i

set @Subscriber Key = AttributeValue("_Subscriber Key") 
set @returnRows = 0 /* 0 means all, max 2000 */
set @rows = LookupOrderedRows("Master_Indiv_Business",@returnRows,"Business_ID asc","Subscriber Key", @Subscriber Key)

set @rowCount = rowcount(@rows)

if @rowCount > 0 then

  var @Business_IDs
  set @Business_IDs = ""

  for @i = 1 to @rowCount do

    var @Business_ID

    set @row = row(@rows,@i) /* get row based on counter */
    set @Business_ID = field(@row,"Business_ID")

    if @i < @rowCount then
      set @Business_IDs = concat(@Business_IDs, @Business_ID,", ")
    else
      set @Business_IDs = concat(@Business_IDs, @Business_ID)
    endif

  next @i 

else 

    write(concat("<br>no rows found"))

endif 
]%%
Hi Business owner.
<br><br>We are just updating our records as we currently
have the following Business IDs on file for you %%=v(@Business_IDs)=%%.

Answer 1

Welcome to SFSE. I'd recommend reviewing all of the Data Extension lookup functions in the official documentation. I also have some examples here on my blog.

I'd approach it like this:

%%[
var @rows, @row, @rowCount, @returnRows, @SubscriberKey, @i

set @SubscriberKey = AttributeValue("_subscriberkey") 
set @returnRows = 0 /* 0 means all, max 2000 */
set @rows = LookupOrderedRows("Master_Indiv_Business", @returnRows, "Business_ID asc", "Subscriber Key", @SubscriberKey)

set @rowCount = rowcount(@rows)

if @rowCount > 0 then

  var @Business_IDs
  set @Business_IDs = ""

  for @i = 1 to @rowCount do

    var @Business_ID

    set @row = row(@rows,@i) /* get row based on counter */
    set @Business_ID = field(@row,"Business_ID")

    if @i < @rowCount then
      set @Business_IDs = concat(@Business_IDs, @Business_ID,", ")
    else
      set @Business_IDs = concat(@Business_IDs, @Business_ID)
    endif

  next @i 

else 

    write(concat("<br>no rows found"))

endif 
]%%
Hi Business owner.
<br><br>We are just updating our records as we currently
have the following Business IDs on file for you %%=v(@Business_IDs)=%%.