Conveting rows to columns

If I have a table that looks like the one below.How do I convet the rows into columns in C#.

Date Datatype outlet AMT

1/12/12 SUM A 80

1/12/12 SUM B 70

1/12/12 SUM C 60

1/12/12 COUNT A 15

1/12/12 COUNT B 15

1/12/12 COUNT C 10

This is how I want it to look ANY HELP. Thanks

Date Datatype A B C

1/12/12 S 80 70 60

1/12/12 C 15 15 12

Suppose dt is your DataTable.

Then the following code should create another DataTable, dt2, on the lines you want:

var dt2 = new DataTable();

DataColumn pk1 = dt2.Columns.Add("Date", typeof(DateTime));

DataColumn pk2 = dt2.Columns.Add("Datatype");

dt2.PrimaryKey = new DataColumn[]{pk1, pk2};

foreach(DataRow dr in dt.Rows)

{

string letter = dr["outlet"].ToString();

if (!dt2.Columns.Contains(letter))

{

dt2.Columns.Add(letter, typeof(int));

}

foreach(DataRow dr in dt.Rows)

{

var date = (DateTime)dr["Date"];

var dataType = dr["Datatype"].ToString().Substring(0,1);

var outlet = dr["outlet"].ToString();

var amt = (int)dr["AMT"];

DataRow dr2 = dt2.Rows.Find(new object[]{date, dataType});

if (dr2 == null)

{

dr2 = dt2.NewRow();

dr2["date"] = date;

dr2["datatype"] = dataType;

dr2[outlet] = amt;

dt2.Rows.Add(dr2);

}

else if (Convert.IsDBNull(dr2[outlet]))

{

dr2[outlet] = amt;

}

else

{

dr2[outlet] = (int)dr2[outlet] + amt;

}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes12y ago
Accepted answer
Suppose dt is your DataTable.

Then the following code should create another DataTable, dt2, on the lines you want:

var dt2 = new DataTable();
DataColumn pk1 = dt2.Columns.Add("Date", typeof(DateTime));
DataColumn pk2 = dt2.Columns.Add("Datatype");

dt2.PrimaryKey = new DataColumn[]{pk1, pk2};

foreach(DataRow dr in dt.Rows)
{
string letter = dr["outlet"].ToString();
if (!dt2.Columns.Contains(letter))
{
dt2.Columns.Add(letter, typeof(int));
}
}

foreach(DataRow dr in dt.Rows)
{
var date = (DateTime)dr["Date"];
var dataType = dr["Datatype"].ToString().Substring(0,1);
var outlet = dr["outlet"].ToString();
var amt = (int)dr["AMT"];
DataRow dr2 = dt2.Rows.Find(new object[]{date, dataType});
if (dr2 == null)
{
dr2 = dt2.NewRow();
dr2["date"] = date;
dr2["datatype"] = dataType;
dr2[outlet] = amt;
dt2.Rows.Add(dr2);
}
else if (Convert.IsDBNull(dr2[outlet]))
{
dr2[outlet] = amt;
}
else
{
dr2[outlet] = (int)dr2[outlet] + amt;
}
}
John Hammond12y ago
Thanks