Group data output using AMPScript

Question

I have a data extension with multiple fields and would like to group the output based on the specific field.

For example the fields on the DE are:

• Product Type
• Interest Rate
• Amount

I would like to group the data outputs based on the product type (rather than repeat the Product Type for each output, for example:

Product Type A

• Interest Rate 1, Amount 1
• Interest Rate 2, Amount 2
• Interest Rate 3, Amount 3

Product Type B

• Interest Rate 4 Amount 4

etc

I have built the code to output the data without grouping (see below), but not sure how to modify this to achieve what I need.

%%[ 
SET @GroupRows = LookupRows("Product Data Extension", "Reference ID",[Reference ID])  

FOR @counter=1 TO RowCount(@GroupRows)  DO
  SET @currentRow=Row(@GroupRows,@counter)
  SET @Amount=Field(@currentRow, "Amount")  
  SET @Rate=Field(@currentRow, "Rate")  
  SET @ProductType=Field(@currentRow, "Product Type") 
]%% 

/* display output fields */

%%[ NEXT @counter ]%%

Any suggestions would be great. Thanks!

Answer 1

You'd just need to keep track of the current Product Type as you're iterating and then output a new heading row when it changes.

For example:

%%[ 
var @i, @rows, @row, @Amount, @Rate, @productType, @prevProductType

SET @rows = LookupRows("Product Data Extension", "Reference ID",[Reference ID])  

set @prevProductType = ""

FOR @i = 1 TO RowCount(@rows) DO
  set @row = Row(@rows,@i)
  set @Amount = Field(@row, "Amount")  
  set @Rate = Field(@row, "Rate")  
  set @ProductType = Field(@row, "Product Type") 

  if empty(@prevProductType) or @productType != @prevProductType then

     set @prevProductType = @productType

]%% 

     <h1>%%=v(@ProductType)=%%</h1>

%%[ else ]%%

     %%=v(@i)=%% %%=v(@Amount)=%% %%=v(@Rate)=%%<br/>

%%[ endif ]%%

%%[ NEXT @i ]%%