Pivot Example

With this example we will try to explain and build pivot grid. We hope it will be clear, so that more user can have idea what is happen. Now - lets do first with the data.

Let’s say that we have a table with sales data with the following fields:

• category name
• product name
• country
• totalprice
• quantity

This can be seen on the picture below:

This grid view is achieved with jqGrid with the following code:

jQuery(document).ready(function(){	
 
	jQuery("#grid").jqGrid(
	{
		url : "data.json",
		loadonce: true,
		colModel : [
			{ name: "CategoryName"},
			{ name: "ProductName" },
			{ name: "Country"},
			{ name: "Price", "formatter": 'number', align: "right"},
			{ name: "Quantity", formatter:'integer', align:"right"}
		],
		datatype:"json",
		width: 700,
		rowNum : 10,
		pager: "#pager",
		caption: "Grid"
	});
});

The data is obtained from Northwind database with the following SQL:

SELECT 
    c.CategoryName, 
    b.ProductName, 
    e.Country, 
    SUM( a.Quantity * a.UnitPrice ) AS Price,
    SUM(a.Quantity) AS Quantity 
FROM 
    order_details a, 
    products b, 
    categories c, 
    orders d, 
    customers e
WHERE 
    a.ProductID = b.ProductID
    AND b.CategoryID = c.CategoryID
    AND a.OrderID = d.OrderID
    AND d.CustomerID = e.CustomerID
    AND (e.Country = 'UK' OR e.Country = 'USA')
GROUP BY 
    a.ProductID, 
    e.Country

The JSON response from the server obtained via ajax ( the option url:“data.json”) has the following structuure:

{"rows":[
{"CategoryName":"Beverages","ProductName":"Steeleye Stout","Country":"UK","Price":"1008.0000","Quantity":"65"},
{"CategoryName":"Beverages","ProductName":"Laughing Lumberjack Lager","Country":"USA","Price":"140.0000","Quantity":"10"},
{"CategoryName":"Beverages","ProductName":"Lakkalik","Country":"USA","Price":"2160.0000","Quantity":"120"},
...
]}

Let say that your boss want to know the sales for the categories and products for the given countries.
Of course this can be achieved with another SQL query, but … your provider does not allow you to add new query or to chenge the existing one. What to do?
You can use the jqPivotGrid for this purpose without to make changes to the server side responses. Let see how to do this.