Question:
I need a C# method that can pivot a DataTable/IDataReader object where the source table contains a composite key.
I wanted to use this: http://weblogs.sqlteam.com/jeffs/articles/5091.aspx however it only works for a single key column.
No SQL. It must be C#.
Solution:1
You could use LINQ:
myDataTable.AsEnumerable() .GroupBy(r => new {col1 = r["col1"], col2 = r["col2"]}); Edit Vertified per comment:
DataTable dataTable = new DataTable(); dataTable.Columns.Add("col1"); dataTable.Columns.Add("col2"); dataTable.Columns.Add("val"); dataTable.Rows.Add("a", "b", 0); dataTable.Rows.Add("a", "b", 2); dataTable.Rows.Add("a", "c", 3); Console.WriteLine(dataTable.AsEnumerable() .GroupBy(r => new { col1 = r["col1"], col2 = r["col2"] }).Count()); //2
Solution:2
Unfortunately I do not know enough about LINQ to determine if this is a viable solution. I have opted to do my pivot logic at the database level. I'll keep my eyes open.
Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
EmoticonEmoticon