Creating a DataTable or DataView with Distinct entries

Question

I have the following XML file

<Department>
   <dept>
      <category>HR</category>
      <performance>8.2</performance>
      <month>3</month>
   </dept>
   <dept>
      <category>Marketing</category>
      <performance>8.8</performance>
      <month>3</month>
   </dept>
   <dept>
      <category>HR</category>
      <performance>7.2</performance>
      <month>4</month>
   </dept>
   <dept>
      <category>Admin</category>
      <performance>8.9</performance>
      <month>4</month>
   </dept>
</Department>

I am able to read the XML into a DataTable and create a DataView out of it. But I want to do the following:

Wherever Category is repeated (like HR is repeated twice), consider only one record and average the performance points. So the record should be like

HR 7.7
Marketing 8.8
Admin 8.9

Here since HR was repeated twice, I averaged it out.

I want to specify this condition while creating a DataTable or DataView out of the XML file. How to do?

Answer 1

Did you try .ToTable()?

DataTable distinctTable = originalTable.DefaultView.ToTable( /*distinct*/ true);

Checkout:

• Select DISTINCT on DataTable
• Or if you're using Linq, see LINQ To DataSet Samples

--EDIT-- http://izlooite.blogspot.com/2010/10/how-to-distinct-sum-count-datatable-for.html

var result = from theRow in dtTable.DataSet.Tables[0].AsEnumerable()
                 group theRow by new
                 {
                     theRow = theRow.Field<string>("category")//Group by category.
                 } into Group
                 select new
                 {
                     Row = Group.Key.theRow,
                     Count = Group.Count(),
                     Average = Group.Average(row => Decimal.Parse(row.Field<string>("performance"))),
                     UniqueRows = (from p in Group
                                   select p.Field<string>("performance")).Distinct().Count()
                 };

Gives:

HR 7.7

Marketing 8.8

Admin 8.9