Convert Rows into Columns using Linq in ASP.Net

Hi EmadKhan,

Refer below code.

Namespaces

C#

using System.Data;

VB.Net

Imports System.Data

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[4] { new DataColumn("ENTRYID"), new DataColumn("COLID"), new DataColumn("COLVALUETEXT"), new DataColumn("COLVALUENUM") });
    dt.Rows.Add(1, 1, "Emad Khan", "");
    dt.Rows.Add(1, 2, "", "3332030201");
    dt.Rows.Add(1, 3, "Karachi", "");
    dt.Rows.Add(1, 4, "abc@gmail.com", "");
    dt.Rows.Add(2, 1, "Danish Khan", "");
    dt.Rows.Add(2, 2, "", "3332030201");
    dt.Rows.Add(2, 3, "Karachi", "");
    dt.Rows.Add(2, 4, "abc@gmail.com", "");
    dt.Rows.Add(3, 1, "Ali Khan", "");
    dt.Rows.Add(3, 2, "", "3332030201");
    dt.Rows.Add(3, 3, "Karachi", "");
    dt.Rows.Add(3, 4, "abc@gmail.com", "");
    DataTable dt1 = new DataTable();
    dt1.Columns.AddRange(new DataColumn[4] { new DataColumn("Name"), new DataColumn("Phone"), new DataColumn("City"), new DataColumn("Email") });
    var result = (from t in dt.AsEnumerable()
                    select t["ENTRYID"]).Distinct().ToArray();
    foreach (string entryId in result)
    {
        DataTable dt3 = dt.Select("ENTRYID=" + entryId).CopyToDataTable();
        string name = dt3.Select("COLID=1")[0][2].ToString();
        string phone = dt3.Select("COLID=2")[0][3].ToString();
        string city = dt3.Select("COLID=3")[0][2].ToString();
        string email = dt3.Select("COLID=4")[0][2].ToString();
        dt1.Rows.Add(name, phone, city, email);
    }

    GridView gvDetails = new GridView();
    gvDetails.DataSource = dt1;
    gvDetails.DataBind();
    form1.Controls.Add(gvDetails);
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim dt As DataTable = New DataTable()
    dt.Columns.AddRange(New DataColumn(3) {New DataColumn("ENTRYID"), New DataColumn("COLID"), New DataColumn("COLVALUETEXT"), New DataColumn("COLVALUENUM")})
    dt.Rows.Add(1, 1, "Emad Khan", "")
    dt.Rows.Add(1, 2, "", "3332030201")
    dt.Rows.Add(1, 3, "Karachi", "")
    dt.Rows.Add(1, 4, "abc@gmail.com", "")
    dt.Rows.Add(2, 1, "Danish Khan", "")
    dt.Rows.Add(2, 2, "", "3332030201")
    dt.Rows.Add(2, 3, "Karachi", "")
    dt.Rows.Add(2, 4, "abc@gmail.com", "")
    dt.Rows.Add(3, 1, "Ali Khan", "")
    dt.Rows.Add(3, 2, "", "3332030201")
    dt.Rows.Add(3, 3, "Karachi", "")
    dt.Rows.Add(3, 4, "abc@gmail.com", "")
    Dim dt1 As DataTable = New DataTable()
    dt1.Columns.AddRange(New DataColumn(3) {New DataColumn("Name"), New DataColumn("Phone"), New DataColumn("City"), New DataColumn("Email")})
    Dim result = (From t In dt.AsEnumerable() Select t("ENTRYID")).Distinct().ToArray()
    For Each entryId As String In result
        Dim dt3 As DataTable = dt.[Select]("ENTRYID=" & entryId).CopyToDataTable()
        Dim name As String = dt3.[Select]("COLID=1")(0)(2).ToString()
        Dim phone As String = dt3.[Select]("COLID=2")(0)(3).ToString()
        Dim city As String = dt3.[Select]("COLID=3")(0)(2).ToString()
        Dim email As String = dt3.[Select]("COLID=4")(0)(2).ToString()
        dt1.Rows.Add(name, phone, city, email)
    Next
    Dim gvDetails As GridView = New GridView()
    gvDetails.DataSource = dt1
    gvDetails.DataBind()
    form1.Controls.Add(gvDetails)
End Sub

Screenshot

This one would be another short approach for this question:

var summary = 
    (from r in records 
    group r by r.EntryID into results
    select new 
    {
        Name  = results.Where(a => a.ColID == 1).Select(a=>a.ColValueText).SingleOrDefault(),
        Phone = results.Where(a => a.ColID == 2).Select(a=>a.ColValueNum ).SingleOrDefault(),
        City  = results.Where(a => a.ColID == 3).Select(a=>a.ColValueText).SingleOrDefault(),
        Email = results.Where(a => a.ColID == 4).Select(a=>a.ColValueText).SingleOrDefault()
    }
    ).ToList();