Click here to Skip to main content
13,195,710 members (53,486 online)
Click here to Skip to main content
Add your own
alternative version


316 bookmarked
Posted 6 Dec 2009

Pivoting DataTable Simplified

, 20 Aug 2014
Rate this:
Please Sign up or sign in to vote.
A class to pivot a DataTable with various aggregate functions.


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 article presents a simplified way to pivot your data with an appropriate aggregate function so that you can enhance your reports easily. Based on the feedback given by various readers, more features have been provided to pivot class. The pivot class is now capable to pivot data on both the axis at a time. Moreover, it also has the facility to do sub-total column wise.  

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. If you wish to do pivot on both the axis, you may use another overload of the same method where you just need to pass RowFields parameter as an array. 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()) })
                       .OrderBy(m => m.Name);

    foreach (var col in ColList)

    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 = 
            for (int i = 0; i < ColumnFields.Length; i++)
                strFilter += " and " + ColumnFields[i] + 
                             " = '" + strColValues[i] + "'";
            row[col.Name.ToString()] = GetData(strFilter, DataField, Aggregate);
    return dt;

PivotData method also has 2 more overloads. If you wish to show column wise sub-total, you may use the overload by passing a bool variable showSubTotal. If you wish to Pivot your data on both side, i.e., row-wise as well as column-wise, you may wish to use another overload where you can pass rowFields and columnFields as an array. 

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)
        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);
                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");

The database used as a sample is an Excel sheet and is present in the "_Data" folder of the root folder of 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();
        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... 

Merge GridView Row Header Cells

This may needs to be done when you are pivoting your data row-wise also. Here we are simply merging the cells with same text.

private void MergeRows(GridView gv, int rowPivotLevel)
        for (int rowIndex = gv.Rows.Count - 2; rowIndex >= 0; rowIndex--)
            GridViewRow row = gv.Rows[rowIndex];
            GridViewRow prevRow = gv.Rows[rowIndex + 1];
            for (int colIndex = 0; colIndex < rowPivotLevel; colIndex++)
                if (row.Cells[colIndex].Text == prevRow.Cells[colIndex].Text)
                    row.Cells[colIndex].RowSpan = (prevRow.Cells[colIndex].RowSpan < 2) ? 2 : prevRow.Cells[colIndex].RowSpan + 1;
                    prevRow.Cells[colIndex].Visible = false;

The code to merge header rows is fairly simple. It simply loops through all the row header cells from bottom to top, compare the text with previous corresponding row cell, increases the row span by 1 if same and hide the previous corresponding row.

Screen shot for both side pivot:

Both Side Pivot

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. Moreover, you may have a deeper look into PivotData method to know how you can search or filter data in DataTable suing linq. Apart from this, MergeRows method acts as a sample to merge rows in a GridView. For beginners, the ExcelLayer.GetDataTable() method will be a sample to get the data from the Excel Sheet.

Based on the request from many readers, i have not provided the sample to query the data from database too. You may find the Sql Script attached to create Sql server database table and code to read data to DataTable from Sql Server.

You may also wish to consider the following link to pivot a DataTable:

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.


  • 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)


About the Author

Anurag Gandhi
India India
Anurag Gandhi currently works in web application design/development and has done so for many years now as he is passionate about programming.
He is extensively involved in Asp.Net and Asp.Net MVC web application architecture, AngularJs, design and development. His languages of choice are C#, Asp.Net, MVC, Asp, C, C++. But he is familiar with many other coding languages. He works with MS Sql Server as the database architecture of choice.
He is active in programming communities and loves to share his knowledge with other developers whenever he gets the opportunity.
He is also a passionate chess player.

He can be contacted at:

You may also be interested in...


Comments and Discussions

QuestionThank you Anurag Gandhi Pin
arulb2w8-Mar-13 19:39
memberarulb2w8-Mar-13 19:39 
AnswerRe: Thank you Anurag Gandhi Pin
Anurag Gandhi9-Mar-13 1:19
memberAnurag Gandhi9-Mar-13 1:19 
QuestionCouple of problems... Pin
cjard4-Feb-13 0:43
membercjard4-Feb-13 0:43 
AnswerRe: Couple of problems... Pin
Anurag Gandhi8-Feb-13 16:57
memberAnurag Gandhi8-Feb-13 16:57 
GeneralMy vote of 5 Pin
Member 760623810-Jan-13 6:31
memberMember 760623810-Jan-13 6:31 
GeneralRe: My vote of 5 Pin
Anurag Gandhi8-Feb-13 16:58
memberAnurag Gandhi8-Feb-13 16:58 
Questionthanks Pin
aj18830-Dec-12 3:09
memberaj18830-Dec-12 3:09 
AnswerRe: thanks Pin
aj18830-Dec-12 10:59
memberaj18830-Dec-12 10:59 
GeneralGood Work Pin
Member 790590310-Dec-12 21:32
memberMember 790590310-Dec-12 21:32 
GeneralRe: Good Work Pin
Anurag Gandhi19-Dec-12 19:57
memberAnurag Gandhi19-Dec-12 19:57 
QuestionWhat about Entity framework Pin
joker220627-Nov-12 3:08
memberjoker220627-Nov-12 3:08 
AnswerRe: What about Entity framework Pin
Anurag Gandhi17-May-13 21:16
memberAnurag Gandhi17-May-13 21:16 
QuestionReport on two values? Pin
geezer9917-Nov-12 16:11
membergeezer9917-Nov-12 16:11 
QuestionElegant, had to modify a bit to handle translation of DataTypes to new table Pin
dternes5-Nov-12 6:39
memberdternes5-Nov-12 6:39 
Great job on this function. I started down the path of creating one, but a quick stack overflow search sent me here, and surely saved me some time.

For my implementation, it was important that the datatypes of the rows and columns be accurate coming back out of the method, so i made a few mods to handle them.

dave Smile | :)
AnswerRe: Elegant, had to modify a bit to handle translation of DataTypes to new table Pin
Anurag Gandhi5-Nov-12 22:11
memberAnurag Gandhi5-Nov-12 22:11 
GeneralRe: Elegant, had to modify a bit to handle translation of DataTypes to new table Pin
johnmcalvert14-May-13 6:26
memberjohnmcalvert14-May-13 6:26 
GeneralRe: Elegant, had to modify a bit to handle translation of DataTypes to new table Pin
Anurag Gandhi24-May-13 20:20
memberAnurag Gandhi24-May-13 20:20 
QuestionHelp with Importing Pivot Pin
HyprHare31-Oct-12 4:50
memberHyprHare31-Oct-12 4:50 
AnswerRe: Help with Importing Pivot Pin
Anurag Gandhi1-Nov-12 9:34
memberAnurag Gandhi1-Nov-12 9:34 
QuestionPlease add complete code Pin
ezar2312-Sep-12 12:15
memberezar2312-Sep-12 12:15 
AnswerRe: Please add complete code Pin
Anurag Gandhi13-Sep-12 21:40
memberAnurag Gandhi13-Sep-12 21:40 
QuestionError using VS 2008 Pin
LeoLisogorsky3-Aug-12 8:17
memberLeoLisogorsky3-Aug-12 8:17 
GeneralRe: Error using VS 2008 Pin
Anurag Gandhi5-Aug-12 7:06
memberAnurag Gandhi5-Aug-12 7:06 
GeneralMy vote of 1 Pin
Kurisingal31-Jul-12 19:41
memberKurisingal31-Jul-12 19:41 
GeneralRe: My vote of 1 Pin
Anurag Gandhi5-Aug-12 7:08
memberAnurag Gandhi5-Aug-12 7:08 
Generalcomments Pin
amrit_wadhwa26-Jun-12 3:12
memberamrit_wadhwa26-Jun-12 3:12 
Questionmarvelous!!! Pin
onurr29-May-12 23:45
memberonurr29-May-12 23:45 
AnswerRe: marvelous!!! Pin
Anurag Gandhi30-May-12 8:36
memberAnurag Gandhi30-May-12 8:36 
QuestionRow Total Column to Right Pin
fmrock29-May-12 6:45
memberfmrock29-May-12 6:45 
GeneralMy vote of 5 Pin
Reza Ahmadi29-May-12 1:37
memberReza Ahmadi29-May-12 1:37 
GeneralRe: My vote of 5 Pin
Anurag Gandhi29-May-12 18:16
memberAnurag Gandhi29-May-12 18:16 
Questionhow to edit the data in pivot Pin
prithvi8210-Apr-12 8:47
memberprithvi8210-Apr-12 8:47 
AnswerRe: how to edit the data in pivot Pin
Anurag Gandhi14-Apr-12 19:29
memberAnurag Gandhi14-Apr-12 19:29 
QuestionWonderful Job Pin
defineconst7-Apr-12 15:49
memberdefineconst7-Apr-12 15:49 
AnswerRe: Wonderful Job Pin
Anurag Gandhi14-Apr-12 19:30
memberAnurag Gandhi14-Apr-12 19:30 
QuestionPivote using multiple key columns Pin
sharmilaKumari15-Feb-12 18:33
membersharmilaKumari15-Feb-12 18:33 
AnswerRe: Pivote using multiple key columns Pin
Anurag Gandhi16-Feb-12 5:29
memberAnurag Gandhi16-Feb-12 5:29 
GeneralMy vote of 5 Pin
manoj kumar choubey7-Feb-12 0:03
membermanoj kumar choubey7-Feb-12 0:03 
GeneralRe: My vote of 5 Pin
Anurag Gandhi15-Feb-12 7:42
memberAnurag Gandhi15-Feb-12 7:42 
GeneralMy vote of 5 Pin
AnniKiran21-Sep-11 22:00
memberAnniKiran21-Sep-11 22:00 
GeneralRe: My vote of 5 Pin
Anurag Gandhi10-Oct-11 20:39
memberAnurag Gandhi10-Oct-11 20:39 
GeneralMy vote of 5 Pin
aaroncampf19-Jul-11 8:43
memberaaroncampf19-Jul-11 8:43 
GeneralRe: My vote of 5 Pin
Anurag Gandhi21-Sep-11 19:24
memberAnurag Gandhi21-Sep-11 19:24 
GeneralMy vote of 5 Pin
enexooone13-Jul-11 23:51
memberenexooone13-Jul-11 23:51 
GeneralRe: My vote of 5 Pin
Anurag Gandhi21-Sep-11 19:24
memberAnurag Gandhi21-Sep-11 19:24 
GeneralVS2005 users Pin
F[]X5-Jun-11 23:03
memberF[]X5-Jun-11 23:03 
GeneralRe: VS2005 users Pin
Anurag Gandhi14-Jun-11 23:46
memberAnurag Gandhi14-Jun-11 23:46 
GeneralMy vote of 2 Pin
rutstyle19-Mar-11 5:00
memberrutstyle19-Mar-11 5:00 
GeneralRe: My vote of 2 Pin
Anurag Gandhi16-May-11 22:15
memberAnurag Gandhi16-May-11 22:15 
QuestionMore than 1 line level Pin
astro__pc11-Jan-11 6:16
memberastro__pc11-Jan-11 6:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.171019.1 | Last Updated 20 Aug 2014
Article Copyright 2009 by Anurag Gandhi
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid