Pivoting DataTable Simplified

• Download source file (C#) - 1.6 KB
• Download demo - 14.4 KB
• Download source [VB.NET] - 1.98 KB
• Download demo [VB.NET] - 15 KB

Introduction

Displaying data in tabular form is an essential part of any application nowadays. But sometimes you need to display a huge amount of data in terms of number of rows. It becomes very difficult to analyse if the number of rows is huge. In such cases, you may wish to summarize your data in the other formats like charts, graphs, groups, pivots, etc. This articles presents a simplified way to pivot your data with an appropriate aggregate function so that you can enhance your reports easily.

Below is a screenshot of pivoted data in a GridView:

How it Works

To simplify the scenario, I have divided the result table into three areas: RowField, DataField, and ColumnFields. Apart from the area, the Pivot class provides you the option to bind your data based on some aggregate functions. The various aggregate options available are:

• Count: Returns the count of matching data
• Sum: Returns the sum of matching data (to get the sum, the type of the DataField must be convertible to decimal type)
• First: Returns the first occurrence of matching data
• Last: Returns the last occurrence of matching data
• Average: Returns the average of matching data (to get the average, the type of the DataField must be convertible to decimal type)
• Max: Returns the maximum value from the matching data
• Min: Returns the minimum value from the matching data
• Exists: Returns "true" if there is any matching data, else "false"

The code mainly contains a class named "Pivot" that takes the DataTable in the constructor. ColumnFields takes as a string array parameter which allows you to pivot data on more than one column. It contains a function called PivotData() which actually pivots your data.

public DataTable PivotData(string RowField, string DataField, 
       AggregateFunction Aggregate, params string[] ColumnFields)
{
    DataTable dt = new DataTable();
    string Separator = ".";
    var RowList = (from x in _SourceTable.AsEnumerable() 
        select new { Name = x.Field<object>(RowField) }).Distinct();
    var ColList = (from x in _SourceTable.AsEnumerable() 
                   select new { Name = ColumnFields.Select(n => x.Field<object>(n))
                       .Aggregate((a, b) => a += Separator + b.ToString()) })
                       .Distinct()
                       .OrderBy(m => m.Name);

    dt.Columns.Add(RowField);
    foreach (var col in ColList)
    {
        dt.Columns.Add(col.Name.ToString());
    }

    foreach (var RowName in RowList)
    {
        DataRow row = dt.NewRow();
        row[RowField] = RowName.Name.ToString();
        foreach (var col in ColList)
        {
            string strFilter = RowField + " = '" + RowName.Name + "'";
            string[] strColValues = 
              col.Name.ToString().Split(Separator.ToCharArray(), 
                                        StringSplitOptions.None);
            for (int i = 0; i < ColumnFields.Length; i++)
                strFilter += " and " + ColumnFields[i] + 
                             " = '" + strColValues[i] + "'";
            row[col.Name.ToString()] = GetData(strFilter, DataField, Aggregate);
        }
        dt.Rows.Add(row);
    }
    return dt;
}

First of all, the function determines the number of rows by getting the distinct values in RowList, and the number of columns by getting the distinct values in ColList. Then, the columns are created. It then iterates through each row and gets the matching values to the corresponding cell based on the aggregate function provided. To retrieve the matching value, the GetData() function is called.

private object GetData(string Filter, string DataField, AggregateFunction Aggregate)
{
    try
    {
        DataRow[] FilteredRows = _SourceTable.Select(Filter);
        object[] objList = 
         FilteredRows.Select(x => x.Field<object>(DataField)).ToArray();

        switch (Aggregate)
        {
            case AggregateFunction.Average:
                return GetAverage(objList);
            case AggregateFunction.Count:
                return objList.Count();
            case AggregateFunction.Exists:
                return (objList.Count() == 0) ? "False" : "True";
            case AggregateFunction.First:
                return GetFirst(objList);
            case AggregateFunction.Last:
                return GetLast(objList);
            case AggregateFunction.Max:
                return GetMax(objList);
            case AggregateFunction.Min:
                return GetMin(objList);
            case AggregateFunction.Sum:
                return GetSum(objList);
            default:
                return null;
        }
    }
    catch (Exception ex)
    {
        return "#Error";
    }
    return null;
}

This function first filters out the matching RowField and ColumnFields data in the DataRow[] array and then applies the aggregate function on it.

Using the Code

Using the code is simple. Create an instance of the Pivot class and then call the PivotData method with the required parameters. The PivotData() method returns the DataTable which can directly be used as the DataSource of the GridView.

DataTable dt = ExcelLayer.GetDataTable("_Data\\DataForPivot.xls", "Sheet1$");
Pivot pvt = new Pivot(dt);

grdPivot.DataSource = pvt.PivotData("Designation", "CTC", 
   AggregateFunction.Max, "Company", "Department", "Year");
grdPivot.DataBind();

The database used as a sample is an Excel sheet and is present in the "_Data" folder of the root folder of the sample application.

Merge GridView Header Cells

The MergeHeader function is created to merge the header cells to provide a simplified look.

private void MergeHeader(GridView gv, GridViewRow row, int PivotLevel)
{
    for (int iCount = 1; iCount <= PivotLevel; iCount++)
    {
        GridViewRow oGridViewRow = new GridViewRow(0, 0, 
          DataControlRowType.Header, DataControlRowState.Insert);
        var Header = (row.Cells.Cast<tablecell>()
            .Select(x => GetHeaderText(x.Text, iCount, PivotLevel)))
            .GroupBy(x => x);

        foreach (var v in Header)
        {
            TableHeaderCell cell = new TableHeaderCell();
            cell.Text = v.Key.Substring(v.Key.LastIndexOf(_Separator) + 1);
            cell.ColumnSpan = v.Count();
            oGridViewRow.Cells.Add(cell);
        }
        gv.Controls[0].Controls.AddAt(row.RowIndex, oGridViewRow);
    }
    row.Visible = false;
}

The function creates a new row for each pivot level and merges accordingly. PivotLevel here is the number of columns on which the pivot is done.

Header gets all the column values in an array, groups the repeated values returned by the GetHeaderText() function, sets the ColumnSpan property of the newly created cell according to the number of repeated HeaderText, and then adds the cell to the GridViewRow. Finally, add the GridViewRow to the GridView.

The GetHeaderText() function returns the header text based on the PivotLevel.

For example, suppose a pivot is done on three ColumnFields, namely, Company, Department, and Year. The result header of the GridView will initially have a header like Company.Department.Year for a PivotLevel 1. GetHeaderText() will return Company. For a PivotLevel 2, GetHeaderText() will return Company.Department. For a PivotLevel 3, GetHeaderText() will return Company.Department.Year, and so on...

Below is the screenshot of the GridView containing the third level pivoted data:

Points of Interest

Along with pivoting the DataTable, the code will also help you to merge the header cells in the desired format in GridView. For beginners, the ExcelLayer.GetDataTable() method will be a sample to get the data from the Excel Sheet.

You may also wish to consider the following link to pivot a DataTable: http://www.codeproject.com/KB/recipes/CsharpPivotTable.aspx.

Future Consideration

Currently, the code can pivot data only for a DataTable. The code will be enhanced to pivot any object derived from an IListSource or ICollection.

History

• First version release: December 09, 2009.
• VB.NET source and demo added: March 19, 2010.

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)