Pivot tables are one of the most powerful features of modern Spreadsheet applications like Microsoft Excel. They provide an easy way to group, aggregate and analyze data. 

In database tables data is represented as records which consist of one or more columns. It is extremely easy to visualize such data via almost any data grid component on the market. However, the value which one can get from a list of records is somewhat limited especially when the number of rows is large. Pivot tables normally consist of Row, Column and Data (or Value) fields. They allow the user to select which columns from the original record set will form each of these three types of fields.

SuperGridView allows developer to bind to a database table, select which table columns will be used in the data binding, as well as which table columns will form the pivot table rows, columns and data (value) fields.

Let's start with a simple scenarios where we have a binding to a databse table. Below is an example of a data bound SuperGridView control which displays the records from the Invoices table of the Northwind database. The sample code uses a data adapter to fill a Dataset, creates a BoundField object for every table column and adds it to the BoundFields collection of SuperGridView. Finally, it sets the DataSource property of SuperGridView and performs the actual data binding.

NwindDataSet_InovicesTableAdapters.InvoicesTableAdapter adapter =  new NwindDataSet_InovicesTableAdapters.InvoicesTableAdapter();

adapter.Fill(nwindDataSet_Inovices.Invoices);

DataTable tbl = this.nwindDataSet_Inovices.Invoices;

foreach (DataColumn col in tbl.Columns)
{

BoundField boundField = new BoundField(col.Caption, col.ColumnName);

superGrid1.BoundFields.Add(boundField);

}

      superGrid1.DataSource = tbl.DefaultView;
      superGrid1.DataBind();

      superGrid1.Refresh();

In this scenario SuperGridView will display all columns and all records from the Invoices table:
 

Now let’s use the same code and modify it to build a pivot table. In this example, we will create a pivot table which represents a Sales report. On Rows we will display the Cities grouped by Country, on Columns we will display the Shipping Company Name, and finally we’re interested to see the number of sales, the total dollar amount of the sales and the average sale amount as data fields. If it sounds complicated, the good news is that SuperGridView allows you to do that with just a few extra lines of code:

NwindDataSet_InovicesTableAdapters.InvoicesTableAdapter adapter = new NwindDataSet_InovicesTableAdapters.InvoicesTableAdapter();

adapter.Fill(nwindDataSet_Inovices.Invoices);

DataTable tbl = this.nwindDataSet_Inovices.Invoices;

grid.BoundPivotRows.Add(new BoundField("Country", "Country"));

grid.BoundPivotRows.Add(new BoundField("City", "City"));
grid.BoundPivotColumns.Add(new BoundField("Carrier", "Shippers_CompanyName"));

grid.BoundPivotValues.Add(new BoundValueField("Count of Sales", "ExtendedPrice", PivotFieldFunction.Count));

grid.BoundPivotValues.Add(new BoundValueField("Amount of Sales", "ExtendedPrice", PivotFieldFunction.Sum));

grid.BoundPivotValues.Add(new BoundValueField("Avg. Sale Amount", "ExtendedPrice", PivotFieldFunction.Average));

grid.PivotValuesOnRows = false;

foreach (DataColumn col in tbl.Columns)
{
BoundField boundField = new BoundField(col.Caption, col.ColumnName);

grid.BoundFields.Add(boundField);
}

grid.DataSource = tbl.DefaultView;
grid.DataMember = "";
grid.DataBind();

grid.RowsHierarchy.ExpandAllItems();
grid.ColumnsHierarchy.ExpandAllItems();
grid.ColumnsHierarchy.AutoResize();
grid.RowsHierarchy.AutoResize();
grid.Refresh();

And here is the result:

As you can see SuperGridView automatically builds hierarchies on rows and on columns and performs proper data aggregation to calculate the value of each cell. You can slightly modify this sample code and create almost any Pivot table.

We hope you will enjoy this cool feature of SuperGridView and most importantly its power, flexibility and simplicity.