Pivoting DataTable Simplified

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 analyze 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()) })
                       .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;
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 sides, 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);
            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 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...
Merge GridView Row Header Cells
This may need to be done when you are pivoting your data row-wise also. Here, we are simply merging the cells with the 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, compares the text with previous corresponding row cell, increases the row span by 1 if same and hides the previous corresponding row.
Screen shot for 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 using 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: C# Pivot Table.
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
	9^th December, 2009: First version release
	19^th March, 2010: VB.NET source and demo added
    Anurag Gandhi is a Freelance Developer and Consultant, Architect, Blogger, Speaker, and Ex Microsoft Employee. He is passionate about programming.

He is extensively involved in Asp.Net Core, MVC/Web API, Node/Express, Microsoft Azure/Cloud, web application hosting/architecture, Angular, AngularJs, design, and development. His languages of choice are C#, Node/Express, JavaScript, Asp .NET MVC, Asp, C, C++. He is familiar with many other programming languages as well. He mostly works with MS SQL Server as the preferred database and has worked with Redis, MySQL, Oracle, MS Access, etc. also.

He is active in programming communities and loves to share the knowledge with others whenever he gets the time for it.

He is also a passionate chess player.

Linked in Profile: https://in.linkedin.com/in/anuraggandhi

He can be contacted at soft.gandhi@gmail.com
Can you give suggestion on what to do if we have multiple DataFields? 
	Himalay Chauhan 20212-Feb-23  14:24





    
Himalay Chauhan 20212-Feb-23  14:24 
In my case each Column Field is connected with different Data Field.

How to create filter and set row value in such case?

Sign In·View Thread 
List<string> rowList = _Source.Select(x => x[rowField].ToString()).Distinct().ToList();


My dataset is huge and this is timing out for me. I do not want to increase the timeout limit here. Please let me know.

Thank you.

Sign In·View Thread 
How big your dataset is? I can try to reproduce the issue for large dataset.

I will check and update once I get this optimized.

Life is a computer program and everyone is the programmer of his own life.


Sign In·View Thread 
Keep it up!

Also, please can your assist on how to color the cells based on cells' values.

Thanks

Sign In·View Thread 
Thanks for appreciation. This article was written a long back when Asp.Net was popular. However, It will also work in your MVC project.

If you are looking for more structured pivot report in an easy way in MVC View, you might want to refer my another article: Pivot Grid in ASP.NET MVC 

Regarding coloring of cells based on cell's value, you may want to look at the rendered Html and apply Javascript/JQuery to color them. The @Model.ReportWithPivot() method renders simple table that is easy to manipulate with few lines of JavaScript/JQuery code.

Life is a computer program and everyone is the programmer of his own life.


Sign In·View Thread 
Can you please explain your problem. I could not understand what are you looking for.

Life is a computer program and everyone is the programmer of his own life.


Sign In·View Thread 
I want to display total of columns data and total of row data in  the table.I have implemented the pivot table but I am not able to get the total of the data.

Please guide me through this.

Sign In·View Thread 
For Entity Framework and MVC version of the Pivot, i wrote another article:

Pivot Grid in Asp.Net MVC

Life is a computer program and everyone is the programmer of his own life.


Sign In·View Thread 
Public Function PivotData2(DataField As String, Aggregate As AggregateFunction, RowFields As String(), ColumnFields As String()) As DataTable
       Dim dt As New DataTable()
       Dim Separator As String = "."
       Dim RowList = _SourceTable.DefaultView.ToTable(True, RowFields).AsEnumerable().ToList()
       For index As Integer = RowFields.Count() - 1 To 0 Step -1
           RowList = RowList.OrderBy(Function(x) x.Field(Of Object)(RowFields(index))).ToList()
       ' Gets the list of columns .(dot) separated.
       Dim ColList = (From x In _SourceTable.AsEnumerable()
                      Select New With {Key .Name = ColumnFields.Select(Function(n) x.Field(Of Object)(n).ToString()) _
                                                    .Aggregate(Function(a, b) (a & Separator & b.ToString()))}).Distinct() _
                                                    .OrderBy(Function(x) x.Name)
       'dt.Columns.Add(RowFields);
       For Each s As String In RowFields
           dt.Columns.Add(s)
       For Each col In ColList
           dt.Columns.Add(col.Name.ToString())
       ' Cretes the result columns.//
       For Each RowName In RowList
           Dim row As DataRow = dt.NewRow()
           Dim strFilter As String = String.Empty
           For Each Field As String In RowFields
               row(Field) = RowName(Field)
               strFilter += (Convert.ToString(" and ") & Field) + " = '" + RowName(Field).ToString() + "'"
           strFilter = strFilter.Substring(5)
           For Each col In ColList
               Dim filter As String = strFilter
               Dim strColValues As String() = col.Name.ToString().Split(Separator.ToCharArray(), StringSplitOptions.None)
               For i As Integer = 0 To ColumnFields.Length - 1
                   filter += " and " + ColumnFields(i) + " = '" + strColValues(i) + "'"
               row(col.Name.ToString()) = GetData(filter, DataField, Aggregate)
           dt.Rows.Add(row)
       Return dt
   End Function


formthis.com


Sign In·View Thread 
Thanks for getting me the updated vb.net version of it.

Now I begun to feel that VB.net is still alive.  

Life is a computer program and everyone is the programmer of his own life.


Sign In·View Thread 
Here is the usage with comments and supporting asp.net methods and  link to javascript rendering...

VB.NET
Public Shared Function pivot(new_file As String, row_fields As String, data_field As String, column_fields As String, the_function As String, the_file As String) As String
    pivot = "0"
    ' Retrieve the data table from Excel Data Source.
    Dim dt As DataTable = ExcelLayer.GetDataTable("_Data\DataForPivot.xls", "Sheet1$")
    Dim pvt As New Pivot(dt), str As String = String.Empty, dt_pivot As DataTable = Nothing, func As Integer = 0
    Select Case the_function
        Case "Count"
            func = 1
        Case "Sum"
            func = 2
        Case "First"
            func = 3
        Case "Last"
            func = 4
        Case "Average"
            func = 5
        Case "Max"
            func = 6
        Case "Min"
            func = 7
        Case "Exists"
            func = 8
    End Select
    'append row field names to array, example" row_fields = "Designation, Year"
    Dim arr_row_fields As Array = row_fields.Split(",")
    Dim iCount As Integer = arr_row_fields.GetUpperBound(0)
    Dim myRowFields(iCount) As String
    Dim loopIndex As Integer = 0
    'build param array
    For i As Integer = 0 To UBound(arr_row_fields)
        myRowFields(loopIndex) = arr_row_fields(i).ToString.Trim
        loopIndex = loopIndex + 1
    'append column field names to array, example" column_fields = "Company, Department"
    Dim arr_col_fields As Array = column_fields.Split(",")
    iCount = arr_col_fields.GetUpperBound(0)
    Dim myColFields(iCount) As String
    loopIndex = 0
    'build param array
    For i As Integer = 0 To UBound(arr_col_fields)
        myColFields(loopIndex) = arr_col_fields(i).ToString.Trim
        loopIndex = loopIndex + 1
    'value, function, rows, columns...
    'dt_pivot = pvt.PivotData("CTC", AggregateFunction.Max, New String[] { "Designation", "Year" }, New String[] { "Company", "Department" });
    'return datatable
    'dt_pivot = pvt.PivotData(row_field, data_field, func, myValues)
    'both side pivot...
    dt_pivot = pvt.PivotData2(data_field, func, myRowFields, myColFields)
    'convert to json
    pivot = GetJson(dt_pivot)
    'on the page I create an html table on the fly; however json parse sorts it in alpha order when I need it in the original order
    'refer to my question/solution on stackoverflow here...
    'http://stackoverflow.com/questions/39625437/jquery-json-to-dynamic-html-table-in-correct-column-order
    str += "["
    For Each myColumn As DataColumn In dt_pivot.Columns
        str += """" + myColumn.ColumnName.ToString + ""","
    Next myColumn
    str = str.TrimEnd(",")
    str += "]"
    'if yes selected then generated tab separated file
    If the_file = "Yes" Then
        'optionally create a file based on dt_pivot
        Dim path As String = HttpContext.Current.Server.MapPath("files/")
        'create new file from the original, swapping it for this
        Dim newdata As String = CreateStream(dt_pivot, True)
        'create a new file name
        Dim sGuid As String = GetRandomPasswordUsingGUID(10)
        new_file = "tmp_" + sGuid & ".tsv"
        'save it
        System.IO.File.WriteAllText(path & new_file, newdata)
    End If
    'return pivot in json format with columns for html table
    Return pivot & "|" & str
End Function
Public Shared Function GetRandomPasswordUsingGUID(ByVal length As Integer) As String
    'Get the GUID
    Dim guidResult As String = System.Guid.NewGuid().ToString()
    'Remove the hyphens
    guidResult = guidResult.Replace("-", String.Empty)
    'Make sure length is valid
    If length <= 0 OrElse length > guidResult.Length Then
        Throw New ArgumentException("Length must be between 1 and " & guidResult.Length)
    End If
    'Return the first length bytes
    Return guidResult.Substring(0, length)
End Function
Public Shared Function CreateStream(ByVal targetTable As DataTable, bFields As Boolean) As String
    Dim myHtmlFile As String = ""
    If (targetTable Is Nothing) Then
        Throw New System.ArgumentNullException("targetTable")
        'Continue.
    End If
    'Get a worker object.
    Dim myBuilder As System.Text.StringBuilder = New System.Text.StringBuilder()
    If bFields = True Then
        ' & vbTab & _
        For Each myColumn As DataColumn In targetTable.Columns
            myBuilder.Append(myColumn.ColumnName & vbTab)
        Next myColumn
        myBuilder.Append(vbCrLf)
    End If
    'Add the data rows.
    For Each myRow As DataRow In targetTable.Rows
        For Each myColumn As DataColumn In targetTable.Columns
            'exclude Class_Name field from HTML Table in Email
            myBuilder.Append(myRow(myColumn.ColumnName).ToString().Trim & vbTab)
        Next myColumn
        myBuilder.Append(vbCrLf)
    Next myRow
    'Get the string for return.
    myHtmlFile = myBuilder.ToString()
    Return myHtmlFile
End Function
Public Shared Function GetJson(ByVal dt As DataTable) As String
    Dim serializer As System.Web.Script.Serialization.JavaScriptSerializer = New System.Web.Script.Serialization.JavaScriptSerializer()
    serializer.MaxJsonLength = Int32.MaxValue
    Dim rows As New List(Of Dictionary(Of String, Object))
    Dim row As Dictionary(Of String, Object)
    For Each dr As DataRow In dt.Rows
        row = New Dictionary(Of String, Object)
        For Each col As DataColumn In dt.Columns
            row.Add(col.ColumnName, dr(col))
        rows.Add(row)
    Return serializer.Serialize(rows)
End Function




    


formthis.com


modified  23-Sep-16 19:30pm.


Sign In·View Thread 
This is a great idea, but performance wise it is very poor. Any ideas why that could be!

I am comparing it to other ways to pivot data. This code looks simple and easy to follow but it has the worst performance of any other code I tested to achieve the same results.


Sign In·View Thread 
Hi Daniel,

Which other code you have tested.

Can you please send me performance result. I will improve the performance of my code as well based on the test result. For small DataTable, it works just fine.

I have published this article 6 yrs back.

I am planning to write another article on pivot on Entity collection using Linq. DataTables are not being used these days.

Life is a computer program and everyone is the programmer of his own life.


Sign In·View Thread 
How do i show two data fields side by side for multiple dates on columns 
	Member 114278644-Feb-15  11:52
Member 114278644-Feb-15  11:52 
I have two datafields that i have to show side by side (response time and availability of several transactions on multiple dates)

Sign In·View Thread 
Re: How do i show two data fields side by side for multiple dates on columns 
	Anurag Gandhi5-Feb-15  3:14
Anurag Gandhi5-Feb-15  3:14 
Multiple data fields is not supported in current version. 

I will update the article and it will be supported soon.

Life is a computer program and everyone is the programmer of his own life.


Sign In·View Thread 
Re: How do i show two data fields side by side for multiple dates on columns 
	Member 114278645-Feb-15  5:00
Member 114278645-Feb-15  5:00 
Thanks for your quick response. So this is something i am trying to achieve. I have some transactions data these transactions are running every 15 mins everyday. I am getting response time and availability of those transactions. i need to show the date wise/hour wise average data of response time and availability in a pivot table side by side for each date/hour.

So the table will look something like this

Transaction Name    01/29/2015 (UTC) 	   	01/30/2015 (UTC)      01/31/2015 (UTC) 

     		  Response  Availability    Response Availability   Response Availability 

Launch	 	   3.60		98.68 		1.36 	100.00 		1.22    100.00 

Search		   0.60 	100.00 		0.54 	100.00 		0.64    100.00 

Verify 		   0.23   	100.00 		0.23 	100.00          0.23    100.00

modified  5-Feb-15 11:24am.


Sign In·View Thread 
Just tried to open the sample c# code and it failed on an incompatibility with current version of Visual Studio.  I am using 2013 desktop (free) with .net 4 and 4.5 installed.

Any guess as to what might be throwing this error or has anyone else figured it out already?

Thanks.

Sign In·View Thread 
If you are using express edition, you would require Visual Studio Express for Web.

http://www.microsoft.com/en-in/download/details.aspx?id=40747[^]

Anurag Gandhi. 
http://www.gandhisoft.com 
Life is a computer program and every one is the programmer of his own life. 
My latest article: Group GridView Data

Sign In·View Thread 
Server Error in '/PivotDataTable_Vb' Application.
Parser Error
Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.
Parser Error Message: Could not load file or assembly 'AjaxControlToolkit' or one of its dependencies. The system cannot find the file specified.
Source Error:
Line 1:  <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
Line 2:
Line 3:  <%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>
Line 4:
Line 5:  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
Source File: /PivotDataTable_Vb/Default.aspx    Line: 3

Web01 2.8:2023-05-13:1