Convert dynamic DataTable Columns to Rows and Rows to Columns using C# and VB.Net in ASP.Net

Last Reply 9 days ago By pandeyism

Posted 9 days ago

hello,

i am using this snippet instead of dummy data on page how can i pass my query

Rotate ASP.Net GridView - Convert GridView Columns to Rows and Rows to Columns

in this snippet it has only 3 column but in my case i have unknow numbers of coulms so based each user name can multiple columns so i want to transpose it to column

result is like

username  column 1 column 2 coulumn 3    column4

A                  a             b             c

B                  a             b              c

coloumn names are unknows so it should dynamicall get the row and pass it to column

You are viewing reply posted by: pandeyism 9 days ago.
Posted 9 days ago

Hi nauna,

Refer below sample.

HTML

<asp:GridView ID="GridView1" CssClass="Grid" HeaderStyle-CssClass="header" runat="server"
        AutoGenerateColumns="true">
    </asp:GridView>
    <br />
    <asp:Button ID="btnConvert1" runat="server" Text="Convert Rows to Columns" OnClick="Convert"
        CommandArgument="1" />
    <asp:Button ID="btnConvert2" runat="server" Text="Convert Columns to Rows" OnClick="Convert"
        CommandArgument="2" Visible="false" />

Namespaces

C#

using System.Data;

VB.Net

Imports System.Data

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        DataTable dt = GetDataBaseRecord();
        ViewState["dt"] = dt;
        BindGrid(dt, false);
    }
}

private DataTable GetDataBaseRecord()
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[] { new DataColumn("username"), new DataColumn("column") });
    dt.Rows.Add("A", "a");
    dt.Rows.Add("A", "b");
    dt.Rows.Add("A", "c");
    dt.Rows.Add("B", "a");
    dt.Rows.Add("B", "b");
    dt.Rows.Add("B", "c");
    dt.Rows.Add("C", "a");
    dt.Rows.Add("C", "b");
    return dt;
}

private void BindGrid(DataTable dt, bool rotate)
{
    GridView1.ShowHeader = !rotate;
    GridView1.DataSource = dt;
    GridView1.DataBind();
    if (rotate)
    {
        foreach (GridViewRow row in GridView1.Rows)
        {
            row.Cells[0].CssClass = "header";
        }
    }
}

protected void Convert(object sender, EventArgs e)
{
    DataTable dt = (DataTable)ViewState["dt"];
    if ((sender as Button).CommandArgument == "1")
    {
        btnConvert1.Visible = false;
        btnConvert2.Visible = true;
        DataTable dt2 = new DataTable();
        DataTable dtgroup = dt.AsEnumerable()
                            .GroupBy(r => new { Col1 = r["username"], Col2 = r["username"] })
                            .Select(g => g.OrderBy(r => r["column"]).First())
                            .CopyToDataTable();
        for (int i = 0; i < dtgroup.Rows.Count + 1; i++)
        {
            dt2.Columns.Add();
        }
        for (int i = 0; i < dtgroup.Columns.Count - 1; i++)
        {
            for (int j = 0; j < dtgroup.Rows.Count + 1; j++)
            {
                dt2.Rows.Add();
                if (j < dtgroup.Rows.Count)
                {
                    if (j == 0)
                    {
                        dt2.Rows[i][j] = dtgroup.Columns["username"].ColumnName;
                        dt2.Rows[i][j + 1] = dtgroup.Columns["column"].ColumnName+" " + (j + 1);
                    }
                    else
                    {
                        dt2.Rows[i][j + 1] = dtgroup.Columns["column"].ColumnName + " " + (j + 1);
                    }
                }
            }
        }
        for (int i = 0; i < dtgroup.Rows.Count; i++)
        {
            dt2.Rows[i + 1][0] = dtgroup.Rows[i]["username"];
        }
        for (int i = 0; i < dtgroup.Rows.Count; i++)
        {
            DataRow[] rows = dt.Select("username='" + dtgroup.Rows[i]["username"] + "'");
            for (int j = 0; j < rows.Length; j++)
            {
                dt2.Rows[i + 1][j + 1] = rows[j][1];
            }
        }
        BindGrid(dt2, true);
    }
    else
    {
        btnConvert1.Visible = true;
        btnConvert2.Visible = false;
        BindGrid(dt, false);
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        Dim dt As DataTable = GetDataBaseRecord()
        ViewState("dt") = dt
        BindGrid(dt, False)
    End If
End Sub

Private Function GetDataBaseRecord() As DataTable
    Dim dt As DataTable = New DataTable()
    dt.Columns.AddRange(New DataColumn() {New DataColumn("username"), New DataColumn("column")})
    dt.Rows.Add("A", "a")
    dt.Rows.Add("A", "b")
    dt.Rows.Add("A", "c")
    dt.Rows.Add("B", "a")
    dt.Rows.Add("B", "b")
    dt.Rows.Add("B", "c")
    dt.Rows.Add("C", "a")
    dt.Rows.Add("C", "b")
    Return dt
End Function

Private Sub BindGrid(ByVal dt As DataTable, ByVal rotate As Boolean)
    GridView1.ShowHeader = Not rotate
    GridView1.DataSource = dt
    GridView1.DataBind()
    If rotate Then
        For Each row As GridViewRow In GridView1.Rows
            row.Cells(0).CssClass = "header"
        Next
    End If
End Sub

Protected Sub Convert(ByVal sender As Object, ByVal e As EventArgs)
    Dim dt As DataTable = CType(ViewState("dt"), DataTable)

    If (TryCast(sender, Button)).CommandArgument = "1" Then
        btnConvert1.Visible = False
        btnConvert2.Visible = True
        Dim dt2 As DataTable = New DataTable()
        Dim dtgroup As DataTable = dt.AsEnumerable().GroupBy(Function(r) New With {Key .Col1 = r("username"), Key .Col2 = r("username")}).[Select](Function(g) g.OrderBy(Function(r) r("column")).First()).CopyToDataTable()
        For i As Integer = 0 To dtgroup.Rows.Count + 1 - 1
            dt2.Columns.Add()
        Next
        For i As Integer = 0 To dtgroup.Columns.Count - 1 - 1
            For j As Integer = 0 To dtgroup.Rows.Count + 1 - 1
                dt2.Rows.Add()
                If j < dtgroup.Rows.Count Then
                    If j = 0 Then
                        dt2.Rows(i)(j) = dtgroup.Columns("username").ColumnName
                        dt2.Rows(i)(j + 1) = dtgroup.Columns("column").ColumnName & " " + (j + 1)
                    Else
                        dt2.Rows(i)(j + 1) = dtgroup.Columns("column").ColumnName & " " + (j + 1)
                    End If
                End If
            Next
        Next
        For i As Integer = 0 To dtgroup.Rows.Count - 1
            dt2.Rows(i + 1)(0) = dtgroup.Rows(i)("username")
        Next
        For i As Integer = 0 To dtgroup.Rows.Count - 1
            Dim rows As DataRow() = dt.[Select]("username='" & dtgroup.Rows(i)("username") & "'")
            For j As Integer = 0 To rows.Length - 1
                dt2.Rows(i + 1)(j + 1) = rows(j)(1)
            Next
        Next
        BindGrid(dt2, True)
    Else
        btnConvert1.Visible = True
        btnConvert2.Visible = False
        BindGrid(dt, False)
    End If
End Sub

Screenshot