Datatabel Rows to Column

Whal

12y1.6kVisual Basic .NET

i dont use sql pivot

sql query return

date total name

01/01/2014 20 mark

10/01/2014 30 daniel

11/01/2014 40 mark

i want to datatable

date mark daniel

01/01/2014 20

10/01/2014 30

11/01/2014 40

3 Replies

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

Vulpes12y ago
To facilitate that, I'd make the "date' column a primary key.

Try this revised code which also deals with the case where there's more than one row with both the same date and the same name:

var dt2 = new DataTable();
DataColumn pk = dt2.Columns.Add("date", typeof(DateTime));
dt2.PrimaryKey = new DataColumn[]{pk};

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

foreach(DataRow dr in dt.Rows)
{
DateTime date = (DateTime)dr["date"];
string name = dr["name"].ToString();
int total = (int)dr["total"];
DataRow dr2 = dt2.Rows.Find(date);
if (dr2 == null)
{
dr2 = dt2.NewRow();
dr2["date"] = date;
dr2[name] = total;
dt2.Rows.Add(dr2);
}
else if (Convert.IsDBNull(dr2[name]))
{
dr2[name] = total;
}
else
{
dr2[name] = (int)dr2[name] + total;
}
}
Whal12y ago
thx Vulpes well how to one row same date

date mark daniel
01/01/2014 20 30
Vulpes12y ago
Suppose dt is the DataTable filled from the SQL query.

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

var dt2 = new DataTable();
dt2.Columns.Add("date", typeof(DateTime));

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

foreach(DataRow dr in dt.Rows)
{
DateTime date = (DateTime)dr["date"];
string name = dr["name"].ToString();
int total = (int)dr["total"];
DataRow dr2 = dt2.NewRow();
dr2["date"] = date;
dr2[name] = total;
dt2.Rows.Add(dr2);
}