Search

Oct 3, 2008

Transfer rows to column in C#

I was having requirement to change result of SQL in such a way that rows become columns, and columns become rows.

PersonName                Year2000               Year2001               Year2002               Year2003
------------------------- ---------------------- ---------------------- ------------------- ---------------
A 1230 4521 5435 5410
B 1330 4231 5435 1200
C 3230 1623 5435 3652
D 4230 2321 5435 1859


And need output as following


SalesYear       A                      B                      C                      D
--------------- ---------------------- ---------------------- ---------------------- ----------------------
Year2000 1230 1330 3230 4230
Year2001 4521 4231 1623 2321
Year2001 5435 5435 5435 5435
Year2001 5410 1200 3652 1859

Mr Jacob wrote post on this which is uses UNPIVOT operator which is in SQL Server 2005, you can find it here.

Here is the contributed code form Abidali Suthar [one of the developer in my team], which does the same thing but in C#.

I added extension method to DataTable, by calling SwapTable method on any DataTable instance; you can get the rows in columns and columns in rows. Lets look at extension method first.

public static class DatatTableExtension
{
/// <summary>
/// Extension method which transform row to column and column to row
/// </summary>
/// <param name="dt">Object on which we have to do operation</param>
/// <returns>Transformed DataTable object</returns>
public static DataTable SwapTable(this DataTable dt)
{
if (!(dt.Columns.Count > 0 && dt.Rows.Count > 0))
return dt;

DataTable dtNew = new DataTable();
dtNew.Columns.Add(dt.Columns[0].ColumnName);

// Creating columns for new DataTable.
// Adding column to new data table having name as first row of old data table
for (int i = 0; i <= dt.Rows.Count - 1; i++)
dtNew.Columns.Add(dt.Rows[i][0].ToString());

DataRow row;
// Swaping the values
for (int k = 1; k < dt.Columns.Count; k++)
{
row = dtNew.NewRow();
row[0] = dt.Columns[k].ToString();
for (int j = 1; j <= dt.Rows.Count; j++)
row[j] = dt.Rows[j - 1][k];

dtNew.Rows.Add(row);
}

return dtNew;
}
}

As we created extension method, its now easy to call it by creating object of DataTable. Lets see with example.

protected void Page_Load(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection("CONNECTION_STRING"))
{
con.Open();
string strSelect = "SELECT_STATEMENT";

SqlCommand cmd = new SqlCommand(strSelect, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
con.Close();

// Bind original datatable
dgBefore.DataSource = dt;
dgBefore.DataBind();

// Bind swapped datatable, calling extension method on datatable5
dgAfter.DataSource = dt.SwapTable();
dgAfter.DataBind();
}
}

3 comments:

Anonymous said...

Thank you Imran, You have resoloved the issue or removing the loop.

Naren said...

Hi Imran,i need this logic to implement but how can we implement it without the Static methosd
Swap()

Please help me..

Solachuddin said...

Thanks you Imran,,,