Introduction:
This article helps to Convert rows into columns in datatable using asp.net.
This code uses a datatable with three columns and six
rows as shown below. After call the method converted datatable, it will give
you the output which is shown in the second.
Here, I have used in an ASP.NET web page and codebehind
vb.net.
Designing Page (Source Code) :
<!DOCTYPE html PUBLIC "-//W3C//DTD
XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title> Convert
rows into columns in datatable using asp.net
/ Transpose rows into columns in datatable with asp.net</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btnConvertValues" runat="server" OnClick="btnConvertValues_Click" Text=" Convert rows into columns in datatable" />
<br />
<br />
<asp:GridView ID="grvOutput" runat="server" AutoGenerateColumns="true">
</asp:GridView>
</div>
</form>
</body>
</html>
VB.NET:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Handles Me.Load
If Not
Page.IsPostBack Then
Dim dtTemp As New DataTable
Dim con As New SqlConnection("server=vignesh;uid=sa;pwd=sa123;database=asp_dotnet")
con.Open()
Dim cmd As New SqlCommand("SELECT b.name,a.subject,a.mark FROM
studentmarkdetails a JOIN studentdetails b ON b.id=a.id", con)
Dim da As New SqlDataAdapter(cmd)
da.Fill(dtTemp)
con.Close()
ViewState("StudentInformation")
= dtTemp
grvOutput.DataSource = dtTemp
grvOutput.DataBind()
End If
End Sub
Protected Sub btnConvertValues_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim dtTemp As DataTable = ViewState("StudentInformation")
Dim dtDistinctRecords As
DataTable = dtTemp.DefaultView.ToTable(True, "subject")
Dim dtStudentName As DataTable = dtTemp.DefaultView.ToTable(True, "name")
Dim dtStudent As New DataTable
dtStudent.Columns.Add("Name")
For Each rows As DataRow In dtDistinctRecords.Rows
dtStudent.Columns.Add(rows("subject"))
Next
For Each row As DataRow In dtStudentName.Rows
Dim dr As DataRow = dtStudent.NewRow()
dr("Name") = row("Name")
Dim dv As New DataView(dtTemp)
dv.RowFilter = "name='" +
row("name").ToString + "'"
Dim dtStudentdtl As DataTable = dv.ToTable()
For i As Integer = 0 To
dtStudentdtl.Rows.Count - 1
Dim colValue As
String = dtStudentdtl.Rows(i)("mark").ToString()
dr(dtStudentdtl.Rows(i)("subject"))
= colValue
Next
dtStudent.Rows.InsertAt(dr, dtStudent.Rows.Count)
Next
grvOutput.DataSource = dtStudent
grvOutput.DataBind()
End Sub
C#.NET:
protected void
Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
DataTable dtTemp = new DataTable();
SqlConnection con = new SqlConnection("server=vignesh;uid=sa;pwd=sa123;database=asp_dotnet");
con.Open();
SqlCommand cmd = new SqlCommand("SELECT b.name,a.subject,a.mark FROM
studentmarkdetails a JOIN studentdetails b ON b.id=a.id", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dtTemp);
con.Close();
ViewState["StudentInformation"]
= dtTemp;
grvOutput.DataSource = dtTemp;
grvOutput.DataBind();
}
}
protected void btnConvertValues_Click(object sender, EventArgs
e)
{
DataTable dtTemp = (DataTable)ViewState["StudentInformation"];
DataTable dtDistinctRecords =
dtTemp.DefaultView.ToTable(true, "subject");
DataTable dtStudentName =
dtTemp.DefaultView.ToTable(true, "name");
DataTable dtStudent = new
DataTable();
dtStudent.Columns.Add("Name");
foreach (DataRow
rows in dtDistinctRecords.Rows)
{
dtStudent.Columns.Add(rows["subject"].ToString());
}
foreach (DataRow
row in dtStudentName.Rows)
{
DataRow dr = dtStudent.NewRow();
dr["Name"] = row["Name"];
DataView dv = new DataView(dtTemp);
dv.RowFilter = "name='" +
row["Name"] + "'";
DataTable
dtStudentdtl = dv.ToTable();
for (int
i = 0; i < dtStudentdtl.Rows.Count; i++)
{
string colValue = dtStudentdtl.Rows[i]["mark"].ToString();
dr[dtStudentdtl.Rows[i]["subject"].ToString()]
= colValue;
}
dtStudent.Rows.InsertAt(dr, dtStudent.Rows.Count);
}
grvOutput.DataSource = dtStudent;
grvOutput.DataBind();
}
Demo:
