Tutorial :DataTable Pivot method with composite key field



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
Previous
Next Post »