Pivot Data in GridView – A Generic Pivot Method with DataTable

This example shows how to “PIVOT” the original data being 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 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!

Print | posted on Sunday, May 10, 2009 9:04 PM |