Geeks With Blogs

News

Microsoft MVP


DZone MVB


Moderator at CodeASP.NET


Quiz Master







free counters
Free counters
Added on January 19,2012


Follow Me @vmsdurano

A bit About Me



Disclaimer
The opinions expressed herein are my own personal opinions and does not represent the opinions of my employers. Nor does it represent the opinion of my dog, because I don’t have one.


Vinz' Blog (ProudMonkey) "Code, Beer and Music ~ my way of being a programmer"

This example shows how to “PIVOT” the original data that is displayed in the GridView.

To start then lets grab two GridViews from the Visual Studio Toolbox and place it to your webform. The ASPX source would look something like this:

  ORIGINAL Table:    
  <asp:GridView ID="GridView1" runat="server">
  </asp:GridView><br /><br />
  PIVOTED Table:
  <asp:GridView ID="GridView2" runat="server"  ShowHeader="false">
  </asp:GridView>

Now, lets create the Generic method for Pivoting the DataTable. Here’s the code block below:

private DataTable PivotTable(DataTable origTable){

        DataTable newTable = new DataTable();
        DataRow dr = null;
        //Add Columns to new Table
        for (int i = 0; i <= origTable.Rows.Count; i++)
        {
            newTable.Columns.Add(new DataColumn(origTable.Columns[i].ColumnName, typeof(String)));
        }

        //Execute the Pivot Method
        for (int cols = 0; cols < origTable.Columns.Count; cols++)
        {
            dr = newTable.NewRow();
            for (int rows = 0; rows < origTable.Rows.Count; rows++)
            {
                if (rows < origTable.Columns.Count)
                {
                    dr[0] = origTable.Columns[cols].ColumnName; // Add the Column Name in the first Column
                    dr[rows + 1] = origTable.Rows[rows][cols];
                }
            }
            newTable.Rows.Add(dr); //add the DataRow to the new Table rows collection
        }
        return newTable;
}

 

As you have seen, the method PivotTable() returns a DataTable and basically accepts a DataTable as the parameter.

Now let’s bind the GridViews with the original Data from the database and with the pivoted data.  Note that I’m using the Northwind database for this demo.

Here are the code blocks below:

private void BindGridView()
{
        SqlConnection connection = new SqlConnection(GetConnectionString());
        try
        {
            connection.Open();
            string sqlStatement = "SELECT Top(5)* FROM Customers";
            SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
            sqlDa.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                //Bind the First GridView with the original data from the DataTable
                GridView1.DataSource = dt;
                GridView1.DataBind();
 
                //Pivot the Original data from the DataTable by calling the
                //method PivotTable and pass the dt as the parameter

                DataTable pivotedTable = PivotTable(dt);
                GridView2.DataSource = pivotedTable;
                GridView2.DataBind();
            }
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Fetch Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        {
            connection.Close();
        }
}

protected void Page_Load(object sender, EventArgs e){
        if (!Page.IsPostBack)
        {
            BindGridView();
        }
}

As you can see, the code above is very straight forward and self explanatory. For more details on Binding GridView with data from database then you can take a look at this example.

Take a look at the screen shot below for the comparison of the page output:


As you have noticed, the Header or Column Names in the Pivoted table are now displayed in the first column of the Grid which indicates that the original data was being transposed.

 

That’s it! Hope you will find this example useful!

Posted on Sunday, May 10, 2009 9:04 PM ADO.NET , ASP.NET , C# , Tips&Tricks , GridView | Back to top


Comments on this post: Pivot Data in GridView - A Generic Pivot Method with DataTable

# re: Pivot Data in GridView - A Generic Pivot Method with DataTable
Requesting Gravatar...
I found that your code had some incorrect lines. I found that the gridview by default was displaying the column name and the value was also in the first row.
The first for should be strictly less than, and your if statement should be based on newtable.
Also the new data column name should be origTable.Rows[i][0].ColumnName

here is my code

private DataTable PivotTable(DataTable origTable)
{
DataTable newTable = new DataTable();
DataRow dr = null;

//Add Columns to new Table
newTable.Columns.Add(new DataColumn(" ", typeof(String)));
for (int i = 0; i < origTable.Rows.Count; i++)
{
newTable.Columns.Add(
new DataColumn(origTable.Rows[i][0].ColumnName, typeof(String)));
}

//Execute the Pivot Method
for (int cols = 1; cols < origTable.Columns.Count; cols++)
{
dr = newTable.NewRow();
for (int rows = 0; rows < origTable.Rows.Count; rows++)
{
if (rows < newTable.Columns.Count)
{
dr[0] = origTable.Columns[cols].ColumnName; // Add the Column Name in the first Column
dr[rows + 1] = origTable.Rows[rows][cols];
}
}
newTable.Rows.Add(dr); //add the DataRow to the new Table rows collection
}
return newTable;
}
Left by Brandon on Oct 16, 2009 3:49 AM

# re: Pivot Data in GridView - A Generic Pivot Method with DataTable
Requesting Gravatar...
Thank you for this excellent post! You really helped me to figure out my project. However, is there a simple way to drop the field names (the original header)? I see that if I remove this line:

dr[0] = origTable.Columns[cols].ColumnName;

the data is removed. However, I still end up with an empty column. Any advice would be greatly appreciated!

Thanks again!
Left by Adam Roessler on Feb 02, 2010 10:27 AM

# re: Pivot Data in GridView - A Generic Pivot Method with DataTable
Requesting Gravatar...
Vinz, thanks so much for your reply. I did try this, but it doesn't take off the header after it's pivoted to gridview2. Any other suggestions?
Left by Adam Roessler on Feb 03, 2010 4:51 AM

# re: Pivot Data in GridView - A Generic Pivot Method with DataTable
Requesting Gravatar...
Sorry to bump my own comment, but I found a solution that works. In case anyone else wants to know:
Add this to the code behind:
protected void OnRowCreated(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Cells[0].CssClass = "hiddencol";
}
else if (e.Row.RowType == DataControlRowType.Header)
{
e.Row.Cells[0].CssClass = "hiddencol";
}
}

Add this to the Gridview:
OnRowCreated="OnRowCreated"

Here are the Css styles referenced:
.hiddencol
{
display:none;
}
.viscol
{
display:block;
}

Thanks again for getting me this far! I appreciate your coding skills and help!
Left by Adam Roessler on Feb 03, 2010 6:30 AM

# re: Pivot Data in GridView - A Generic Pivot Method with DataTable
Requesting Gravatar...
http://www.codeproject.com/KB/aspnet/PivotDataTable.aspx

This is an another approach. I hope you will like it.

Left by Anurag Gandhi on Feb 13, 2010 2:00 PM

# re: Pivot Data in GridView - A Generic Pivot Method with DataTable
Requesting Gravatar...
*
sqlDa.Fill(dt);
*
actually where does this 'dt' variable comes from.. because i get an error says that the 'dt' is not declare.
it is a bit confusing because you suddenly put that 'dt' in the BindGridview() method..
could someone please explain to me..
tq!
Left by min on Mar 30, 2010 10:45 PM

# re: Pivot Data in GridView - A Generic Pivot Method with DataTable
Requesting Gravatar...
i have tried your code, but nothing happen when i load the web page. i guest it did not read the BindGridview() function..i don't know why.. i had tried to change the connection string to the wrong one. but no error occurs..that's why i assumed that it did not read the bindgridview() function..
perhaps you heve the answer..
Left by lynn on Mar 31, 2010 6:59 AM

# re: Pivot Data in GridView - A Generic Pivot Method with DataTable
Requesting Gravatar...
Vinz,
im having a pivot problem. from what i can see here, you basically did inverting, where rows become columns, and columns become rows. I have a slightly different problem.

If you could just refer to this thread > http://forums.asp.net/t/1545433.aspx
and help me, i would appreciate it alot.

Thank you.
Left by gurubhullar on Apr 10, 2010 4:59 PM

# re: Pivot Data in GridView - A Generic Pivot Method with DataTable
Requesting Gravatar...
System.IndexOutOfRangeException: Cannot find column 3.

Source Error:

Line 31: for (int i = 0; i <= origTable.Rows.Count; i++)
Line 32: {
Line 33: newTable.Columns.Add(new DataColumn(origTable.Columns[i].ColumnName, typeof(String)));
Line 34: }
Line 35:

can you help me fix it, my table on database just have 3 cols!
thks!
Left by mrhahn on Dec 10, 2010 10:04 PM

# re: Pivot Data in GridView - A Generic Pivot Method with DataTable
Requesting Gravatar...
iam getting error "could not find column1 "
Left by Rajesh on Dec 25, 2010 3:02 PM

# re: Pivot Data in GridView - A Generic Pivot Method with DataTable
Requesting Gravatar...
how we use radio button in pivoted gridview??????????
Left by Nidhi Sachan on May 30, 2012 9:30 PM

# re: Pivot Data in GridView - A Generic Pivot Method with DataTable
Requesting Gravatar...
Spent the time and corrected the data return row. The following is an update to the method.

// Method PivotTable() returns DataTable and basically accepts a DataTable as the parameter.
private DataTable PivotTable(DataTable origTable)
{
DataTable newTable = new DataTable();
DataRow dr = null;

//Add Columns to new Table
for (int i = 0; i <= origTable.Rows.Count; i++)
{
newTable.Columns.Add(new DataColumn(origTable.Columns[i].ColumnName, typeof(String)));
}

//Execute the Pivot Method
for (int cols = 0; cols < origTable.Columns.Count; cols++)
{
dr = newTable.NewRow();

// for (int rows = 0; rows <= origTable.Rows.Count; rows++)
for (int rows = 0; rows <= origTable.Rows.Count + 1; rows++)
{
if (rows < origTable.Rows.Count)
{
// Add Column Name in the first Column (currently removing first row of data)
dr[0] = origTable.Columns[cols].ColumnName;
dr[rows + 1] = origTable.Rows[rows][cols];
}
}
newTable.Rows.Add(dr); //add the DataRow to the new Table rows collection
}

newTable.Rows[0].Delete(); // Remove first row ID
return newTable;
}
Left by MB on Oct 27, 2012 5:56 AM

# re: Pivot Data in GridView - A Generic Pivot Method with DataTable
Requesting Gravatar...
private DataTable PivotTable(DataTable origTable)

{
int f= 1;
DataTable newTable = new DataTable();
DataRow dr = null;
//Add Columns to new Table
for (int i = 0; i <= origTable.Rows.Count ; i++)
{
newTable.Columns.Add(new DataColumn(f.ToString(), typeof(String))); f++;
}

//Execute the Pivot Method
for (int cols = 0; cols < origTable.Columns.Count; cols++)
{
dr = newTable.NewRow();
for (int rows = 0; rows < origTable.Rows.Count; rows++)
{
if (rows < newTable.Columns.Count)
{
dr[0] = origTable.Columns[cols].ColumnName; // Add the Column Name in the first Column
dr[rows + 1] = origTable.Rows[rows][cols];
}
}
newTable.Rows.Add(dr); //add the DataRow to the new Table rows collection
} newTable.Rows[0].Delete();
return newTable;
}
Left by Partha on Feb 03, 2013 3:10 AM

comments powered by Disqus

Copyright © Vincent Maverick Durano | Powered by: GeeksWithBlogs.net