How to merge the result from database call?

Question

I have a web report needs to return multiple products from different regions. The simplified query will be like:

Select ProductName, ProductPrice 
from Product
where Region in (Region1, Region2, Region 3)

The regions are selected by users from UI. The result is bound to a datagrid. It could be multiple regions. But the result will be look like

        ProductName,  ProductPrice [Region 1] ProductPrice [Region 2] ...
Prod1
Prod2
....

Products are not as same as in the same region. Some might be null in some regions. To dynamically bound to a grid, I am using a DataTable, then dynamically add datacolumn for the region fields. The easiest approach is to loop the result queried by every region, then merge by the Product (1, 2, 3...) It works, but slow, especially for rows more than 2K. I am wondering if any way we can avoid doing nested loop.

BTW, it is a .NET 2.0 application.

Thanks a lot!

Answer 1

I think we may need a little more information about the queries and the loop you are using, but can you use AJAX? Sounds like you want to make the UI experience more enjoyable and could get the information for Regions "on demand"

Answer 2

Sorry, the first answer below was a dud. If I understand correct, you could fill DataRows in product order, looping the resultset of:

Select ProductName, ProductPrice, Region 
from Product
where Region in (Region1, Region2, Region 3)
ORDER BY ProductID, Region

A new DataRow is created for each new productID, and each region has a DataColumn with ProductPrice value (or nothing).

---

Essential details are missing from the question, but in general I suggest letting the DBMS handle aggregations (if you are using a database?). Two dimensional aggregates should be fast.

1. In the SQL query group by region and by product and use an aggregate function to calculate your amount (e.g. money, quantity). You should get a resultset of (product, group, amount) tuples, where amount is the desired aggregate, or NULL
2. In ASP.NET loop through the resultset and build the datatable
3. Bind the datatable to the grid control

Even if this approach would not be suitable, try to minimize the number of queries from your data source. The number of queries should never be dependent on the grid column/row count.

Answer 3

Are you using SQL Server? If so, you can use the PIVOT operator to pivot on the region.