Please try the following:
<!--- pseudo query --->
<cfscript>
report = queryNew("productTypeName,paintType,paintColor,paintCode,quantity,litreName");
queryAddRow(report, [["Honey","Texture","Brilliant White",1700,3,"20 litres"]]);
queryAddRow(report, [["Honey","Texture","Off White",1701,8,"20 litres"]]);
queryAddRow(report, [["Magic","Texture","Off White",1701,21,"20 litres"]]);
queryAddRow(report, [["Magic","Texture","Brilliant White",1700,8,"20 litres"]]);
queryAddRow(report, [["Princess","Gloss","Brilliant White",9102,9,"4 litres"]]);
queryAddRow(report, [["Princess","Texture","Rose Pink",1712,3,"20 litres"]]);
queryAddRow(report, [["Princess","Texture","Ivory",1704,1,"20 litres"]]);
queryAddRow(report, [["Princess","Texture","Off White",1701,3,"20 litres"]]);
queryAddRow(report, [["Princess","Texture","Off White",1701,3,"20 litres"]]);
</cfscript>
<!--- add groupRowspan and groupTotalQuantity columns --->
<cfscript>
queryAddColumn(report, "groupRowspan", "integer", []);
queryAddColumn(report, "groupTotalQuantity", "integer", []);
if(report.RecordCount) {
lastQueryRowToUpdate = 0;
lastProductType = lastPaintType = lastLitreName = "";
groupRowspan = 0;
groupTotalQuantity = 0;
for(rowNum=1; rowNum<=report.RecordCount; rowNum++) {
if((report.productTypeName[rowNum] is not lastProductType) or (report.paintType[rowNum] is not lastPaintType) or (report.litreName[rowNum] is not lastLitreName)) {
if(lastQueryRowToUpdate) {
querySetCell(report, "groupRowspan", groupRowspan, lastQueryRowToUpdate);
querySetCell(report, "groupTotalQuantity", groupTotalQuantity, lastQueryRowToUpdate);
}
lastQueryRowToUpdate = rowNum;
lastProductType = report.productTypeName[rowNum];
lastPaintType = report.paintType[rowNum];
lastLitreName = report.litreName[rowNum];
groupRowspan = 0;
groupTotalQuantity = 0;
}
groupRowspan++;
if(isValid("integer", report.quantity[rowNum])) {
groupTotalQuantity += report.quantity[rowNum];
}
if((rowNum is report.RecordCount) and lastQueryRowToUpdate) {
querySetCell(report, "groupRowspan", groupRowspan, lastQueryRowToUpdate);
querySetCell(report, "groupTotalQuantity", groupTotalQuantity, lastQueryRowToUpdate);
}
}
}
</cfscript>
<!--- table w/ rowspan --->
<table>
<tr style="background-color:#cce;">
<th>ITEM</th>
<th>QUANTITY</th>
<th>DESCRIPTION</th>
<th>REMARKS</th>
</tr>
<cfloop query="report">
<tr style="background-color:#ccc;">
<cfoutput>
<td>#report.CurrentRow#</td>
<td>#report.quantity#</td>
<td>#report.productTypeName# #report.paintType# #report.paintColor# #report.paintCode#</td>
<cfif isValid("integer", report.groupRowspan)>
<td rowspan="#report.groupRowspan#">#report.groupTotalQuantity# bags of #report.productTypeName# #report.paintType# with #report.litreName#</td>
</cfif>
</cfoutput>
</tr>
</cfloop>
</table>
Thanks!,
-Aaron