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 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.

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.

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.

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.

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:

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.

Share

About the Author

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.

Comments and Discussions

I would suggest you to use it in your scenario and share the performance result.
I will try my best to improve performance if you face any performance issue.
Till now, no one has complained about performance. Anyways, it should be better than t-sql (my openion although i have not tested it).

Dear Anurag,
Your article and code is fantastic . But I think it'll solve only the case when there is only a single data-field column(the column whose values you're summing or aggregating). I'm currently faced with a problem, where there are three columns whose data is to summed up. Is that possible by modifying your Pivot.cs?
Thank you,
Anoop.P.A

I pulled my data from Mysql and as we all know, it doesn't have a simple way to pivot. You just saved me a ton of time and I really appreciate it.

I do have a question though. I'm manipulating my pivoted datatable to add a 'total' column to sum the row (columns 1-n). Is there an easy way to add that to the pivot function? I know you have it to add a total at the bottom (sum of the column), but I need it to the right (sum of a row of columns). Any ideas? Thanks

Hi Anurag, because the design for your component involves keeping an instance field reference to the target DataTable, this strongly suggests the Pivot class should implement IDisposable since DataTable does. For any readers not familiar with why, refer to Visual Studio Code Analysis rule CA1001: Types that own disposable fields should be disposable.

An alternative would be to not cache a reference to the target DataTable in a field. As far as I can see there is no specific reason to cache the reference. It would mean passing in the DataTable with each call to PivotData().

Another alternative would be to implement the PivotData() method as an extension method for DataTable.

The latter is my preferred option as it makes for the cleanest, most readable code for the consumer of the Pivot class. I plan to implement that shortly.

Let me know your thoughts and whether you would like me to post my revised version of the Pivot class.

Thanks John for your nice feedback.
Pivot data as an extension method is already there in my private Extension Method Library.

Many people (including me ) love the extension method approach but in this case it would lack some flexibility which i am planning to add in class approach in future. I would really appreciate 1st and 3rd option and would like go for both the approach.
I would request you to post your revised version here itself as it will help other users as well.
I will update my article accordingly in my next update.

it was a really nice tutorial, i was looking for the same, thank you very much for that, but had a question, i can see that you have shown both side pivot screenshot in the article but your sample code doesn't contain any example for that, looked into the code of pivot method but unable to figure out how you are handling that can you please guide me how can use your code to implement both side pivot.

Hi, not sure what you mean by "the pivot data header" but maybe this example will help. In my case I want to sort on one of the RowFields but the author's code does not preserve the data types of these fields. So proper numeric sorting is not possible. I made a small change in the third overload of PivotData as follows:

i saw your article "Pivoting DataTable Simplified" which is very use full to me. i got result in datatable instead of gridview. i want export that result in one excel sheet means how to do. i don't know how to merge the row header and got your result result in excel sheet. if your feel free help me friend. once again thank U very much...

the ColumnFields array argument, contains an apostrophe, there's an error on this line of GetData():

DataRow[] FilteredRows = _SourceTable.Select(Filter);

-

If the column name text contains a period "." then there is no output, because the filter is composed by splitting the column name based on "." (Pivot.cs line 90)
This makes it a nuisance to work with filenames in column headers

Recommend to move the separator to be a property (settable) and throw an exception if the input column name contains the separator, or replace it with something else

Hi,
These are the nice point mentioned by you. Thank you.
For separator, we have a separator variable where i gave the developer whole right to change the separator which is probably not present in their data column name. You may try separator as | (pipe) or ^ (caret) or any other character.
I feel throwing exception in such case is not a good idea. Exposing it as a Field and replacing it with any unused Unicode character looks very good to me.

For column names that contains apostrophe, replace it with double apostrophe and it should work fine. OR as a workaround, you may wish to restrict user not to use apostrope in their data table.

Thanking you again for pointing it out. I will implement it in my next update.

Hi,
Thanks for the feedback.
There should not be any problem with the no. of rows in DataTable. Count and Row data is processed at runtime. Please let me know if you are facing any issue while using it.

Hi Anurag, to preserve data type I changed two blocks of code. This is a must have enhancement to your project as otherwise the PivotData function degrades the information content of the input data table.

Ideally the pivoted field/column data type should be a parameter but I don't need that so didn't go that far.

Another nice enhancement would be to convert the function into an extension method of the DataTable class but I don't know if that is feasible.

If you want the entire file as an email or attachment let me know.

If you make a collaboration project to share editing the code maybe we could bring those enhancements to life.