Rotate DataTable : Convert Rows to Columns and Columns to Rows using C# .Net

Last Reply on Nov 24, 2014 07:51 AM By Azim

Posted on Nov 24, 2014 07:31 AM
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;
}
Posted on Nov 24, 2014 07:51 AM

For using your code i have added a Class in App_Code folder. Then i have made this class as Static

Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

/// <summary>
/// Summary description for PivoteDataTable
/// </summary>
public static class PivoteDataTable
{

    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;
    }
}

HTML

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        body
        {
            font-family: Arial;
            font-size: 10pt;
        }
        table
        {
            border: 1px solid #ccc;
        }
        table th
        {
            background-color: #F7F7F7;
            color: #333;
            font-weight: bold;
        }
        table th, table td
        {
            padding: 5px;
            border-color: #ccc;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        Normal GridView
        <asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"
            runat="server" AutoGenerateColumns="false">
            <Columns>
                <asp:BoundField DataField="Id" HeaderText="Id" ItemStyle-Width="30" />
                <asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="150" />
                <asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="150" />
            </Columns>
        </asp:GridView>
        Pivoted GridView
        <asp:GridView ID="GridView2" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"
            runat="server" AutoGenerateColumns="true">
        </asp:GridView>
    </div>
    </form>
</body>
</html>

Namespace

using System.Data;

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                        new DataColumn("Name", typeof(string)),
                        new DataColumn("Country",typeof(string)) });
        dt.Rows.Add(1, "John Hammond", "United States");
        dt.Rows.Add(2, "Mudassar Khan", "India");
        dt.Rows.Add(3, "Suzanne Mathews", "France");
        dt.Rows.Add(4, "Robert Schidner", "Russia");
        GridView1.DataSource = dt;
        GridView1.DataBind();
        DataTable dt2 = PivoteDataTable.Pivot(dt);
        GridView2.DataSource = dt2;
        GridView2.DataBind();
    }
}

Screenshot