gridview is showing numbers in the header instead of the columns asp.net c#

Question

I have the below code that shows in a gridview columns as rows:

Code Behind:

protected void Page_Load(object sender, EventArgs e)
{
    gvOrdersProcessed.DataSource = FlipDataTable(BindOrdersProcessed());
    gvOrdersProcessed.DataBind();
}

public DataTable BindOrdersProcessed()
{
    SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["WO_StatusConnectionString"].ToString());
    SqlCommand Cmd = new SqlCommand("sp_B2B_GetOrdersProcessed", Conn);
    Cmd.CommandType = CommandType.StoredProcedure;           

    SqlDataAdapter ada = new SqlDataAdapter(Cmd);
    DataTable dt = new DataTable();
    ada.Fill(dt);
    return dt;
}

public DataTable FlipDataTable(DataTable dt)
{
     DataTable table = new DataTable();

     //Get all the rows and change into columns
     for (int i = 0; i <= dt.Rows.Count; i++)            
         table.Columns.Add(Convert.ToString(i));            

     DataRow dr;

     //get all the columns and make it as rows
     for (int j = 0; j < dt.Columns.Count; j++)
     {
          dr = table.NewRow();
          dr[0] = dt.Columns[j].ToString();
          for (int k = 1; k <= dt.Rows.Count; k++)
              dr[k] = dt.Rows[k - 1][j];
          table.Rows.Add(dr);
     }

     return table;
}

And in my html:

<div id="GridOrdersProcessed">
        <asp:GridView ID="gvOrdersProcessed" runat="server">
        </asp:GridView>
    </div>  

Now when run the above I have numbers as header:

0                    1          2          3          4          5          6 
DateCreate           02/11/2013 02/19/2013 02/21/2013 02/25/2013 02/26/2013 02/27/2013 
OrdersPendingInvoice 0          0          0          0          0          0 
OrdersPendingPickUp  1          1          2          1          7          9 

Why is that happening?

Also, how can I add a new column with the total Orders like below:

DateCreate           Total 02/11/2013 02/19/2013 02/21/2013 02/25/2013 02/26/2013 02/27/2013 
OrdersPendingInvoice 0     0          0          0          0          0          0 
OrdersPendingPickUp  21    1          1          2          1          7          9

Answer 1

You are getting numbers as headers because you have created DataColumns with numeric names here:

public DataTable FlipDataTable(DataTable dt)
{
    DataTable table = new DataTable();
    //Get all the rows and change into columns
    for (int i = 0; i <= dt.Rows.Count; i++)
        table.Columns.Add(Convert.ToString(i));

You probably want somthing like this:

for (int i = 0; i < dt.Rows.Count; i++) 
     table.Columns.Add(Convert.ToString(dt.Rows[i][0]));

Note also that i've changed <= to i < in the loop.

Edit: But i'm afraid that the method does not what it's supposed to do(reverse the roles of rows and columns also called pivot).

You can use this extension method:

public static DataTable Pivot(this DataTable tbl)
{
    var tblPivot = new DataTable();
    tblPivot.Columns.Add(tbl.Columns[0].ColumnName);
    for (int i = 1; i < tbl.Rows.Count; i++)
    {
        tblPivot.Columns.Add(Convert.ToString(i));
    }
    for (int col = 0; col < tbl.Columns.Count; col++)
    {
        var r = tblPivot.NewRow();
        r[0] = tbl.Columns[col].ToString();
        for (int j = 1; j < tbl.Rows.Count; j++)
            r[j] = tbl.Rows[j][col];

        tblPivot.Rows.Add(r);
    }
    return tblPivot;
}

Update Maybe this linq queries helps to get your total column before you pivot the table:

int OrdersPendingInvoiceTotal= tbl.AsEnumerable()
    .Sum(r => r.Field<int>("OrdersPendingInvoice"));
int OrdersPendingPickUpTotal= tbl.AsEnumerable()
    .Sum(r => r.Field<int>("OrdersPendingPickUp"));