Combine 1 field from multiple rows via AmpScript

Question

I have a data extension which contains rows and columns such as:

emailAddress     orderNumber   firstName   lastName   customerOrder
[email protected]  1111          Bill        Adams      2 brown shoes
[email protected]  1111          Bill        Adams      2 green socks
[email protected]  1111          Bill        Adams      1 orange backpack
[email protected]  2222          Bill        Adams      2 pink gloves
[email protected]  3333          David       Sherwood   5 yellow hats

What I'm trying to do is to create an order received email from this data, preferably without altering it from the source. So ideally the email output would group the customerOrder for each customer, based on the orderNumber. Then the customerOrder is concatenated and inserted into an email (note the above is simplified quite a bit, the customerOrder is actually HTML for insertion into an HTML table within the email).

So far I've been able to make this much very basic progress:

%%[
Set @customerOrder = 
LookupOrderedRows("transactionsList",
"0",
"customerOrder",
"orderNumber",
"1111")
]%%

With this code I can see that I have 3 entries for order number 1111. But now I'm stuck. Do I need to create an if/then loop? Or is there some way to take the output from the LookupOrderedRows function and parse it for use in the HTML table within the email?

Answer 1

Using one of the lookup examples on my blog, you can do something like this:

%%[
var @rows, @row, @rowCount, @numRowsToReturn, @emailAddress, @i, @prevOrderNumber

set @emailAddress = AttributeValue("emailaddr")
set @numRowsToReturn = 0 /* 0 means all */
set @rows = LookupOrderedRows("transactionsList", @numRowsToReturn, "orderNumber", "emailAddress", @emailAddress)

set @rowCount = rowcount(@rows)

if @rowCount > 0 then

    set @prevOrderNumber = ""

    for @i = 1 to @rowCount do

        var @orderNumber, @firstName, @lastName, @customerOrder
        set @row = row(@rows,@i) /* get row based on loop counter */

        set @orderNumber = field(@row,"orderNumber")
        set @firstName = field(@row,"firstName")
        set @lastName = field(@row,"lastName")
        set @customerOrder = field(@row,"customerOrder")

        /* output headings for first order or when order # changes */
        if empty(@prevOrderNumber) or @prevOrderNumber != @orderNumber then 
           outputline(concat("<br>Order #:", @orderNumber))
           outputline(concat("<br>Name: ", @firstName, " ", @lastName))
           outputline(concat("<br>Line items:<br>"))
           set @prevOrderNumber = @orderNumber
        endif

        outputline(concat("<br>",@customerOrder))

    next @i

else 

    outputline(concat("<br>No transactionsList rows found"))

endif 
]%%