Datatabel Rows to Column

Question

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

Vulpes · Answer

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); }

Vulpes · Answer

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; } }

Whal · Answer

thx Vulpes well how to one row same date date mark daniel 01/01/2014 20 30