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.

Answer 1

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