title: How to generate a Pivot Datagridview in WinForms and EntityFramework
body: <p>I want to generate a Pivot Datagridview in WinForms and EntityFramework, starting from theses Entities:</p>
  
  <pre><code>public class Return
  {
      public Return()
      {
          this.ReturnQty = new List&lt;ReturnQty&gt;();
      }
  
      public int Id { get; set; }
      public Nullable&lt;System.DateTime&gt; Date { get; set; }
      public Nullable&lt;int&gt; ReturnReason { get; set; }
      public Nullable&lt;System.DateTime&gt; Belastungsdatum { get; set; }
      public virtual ICollection&lt;ReturnQty&gt; ReturnQty  { get; set; }
  }
  
  public class ReturnQty
  {
      public int ID { get; set; }
      public int Return_ID { get; set; }
      public string ItemNo { get; set; }
      public Nullable&lt;decimal&gt; Qty { get; set; }
      public virtual Return Return { get; set; }
  }
  </code></pre>
  
  <p>The Pivot Dataviewgrid should look like this:</p>
  
  <p><img src="https://i.stack.imgur.com/C1BtW.jpg" alt="enter image description here"></p>
  
  <p>This is what I have now:</p>
  
  <pre><code>public static class ReturnReasons
  {
  
      public static string a { get { return "Grund nicht erfasst"; } }
      public static string a1 { get { return "Artikel mangelhaft"; } }
      public static string a2 { get { return "Bestellirrtum Kunde"; } }
      public static string a3 { get { return "Doppelbestellung Kunde"; } }
      public static string a4 { get { return "AV Kunde"; } }
      public static string a5 { get { return "Kundenadresse falsch"; } }
      public static string a6 { get { return "Kunde nicht erreichbar"; } }
      public static string a7 { get { return "Kundenstorno"; } }
      public static string a8 { get { return "Nichtgefallen"; } }
      public static string a9 { get { return "Transportschaden UPS/DHL/GLS"; } }
      public static string a10 { get { return "Transportschaden Spedition"; } }
      public static string a11 { get { return "Transportschaden verdeckt"; } }
      public static string a12 { get { return "Kommissionierungsfehler"; } }
      public static string a13 { get { return "Fehler Auftragserfasung"; } }
      public static string a14 { get { return "Lieferverzögerung"; } }
      public static string a15 { get { return "Warenrücksendung lt. Vereinbarung"; } }
      public static string a16 { get { return "ohne Grund/sonstiges"; } }
  }
  
          public class RetourenPivot
      {
          public string Item{ get; set; }
          public IEnumerable&lt;int?&gt; ReturnReason{ get; set; }
          public IEnumerable&lt;decimal?&gt; Qty{ get; set; }
      }
  
          private void DG_databind()
      {
          var query = _data.RepositoryRetouren.GetAll&lt;ReturnQty&gt;();
          // A Linq to EF which creates a List of concreate class called RetourenPivot.
          var queryResults = (from iso in query
                              orderby iso.ItemNo ascending
                              group iso by iso.ItemNo into isoGroup
                              select new RetourenPivot()
                              {
                                  Item = isoGroup.Key,
                                  ReturnReason = isoGroup.Select(y =&gt; y.Return.ReturnReason),
                                  Qty = isoGroup.Select(v =&gt; v.Qty)
                              }).ToList();
  
  
          // Call a function to create a dynamically created data table with the needed columns
  
          // Create a DataTable as a DataSource for the grid
          DataTable dt = new DataTable();
          // Create the DataColumns for the data table
          DataColumn dc = new DataColumn("Artikel", typeof(string));
          dt.Columns.Add(dc);
  
          // Get a list of Distinct Reasons
          var ReasonLabel = (from yList in queryResults.Select(Reason =&gt; Reason.ReturnReason)
                             from Reason in yList
                             select Reason.ToString()).Distinct().ToList();
  
          // Create the DataColumns for the table
          ReasonLabel.ForEach(delegate(string Reason)
          {
              var reasonTexts = typeof(ReturnReasons).GetProperties().Where(p =&gt; p.Name == "a" + Reason).ToList()[0].GetValue(null, null).ToString();
              dc = new DataColumn(reasonTexts, typeof(string));
              dt.Columns.Add(dc);
          });
  
          // Populate the rowa of the DataTable
          foreach (RetourenPivot rec in queryResults)
          {
              // The first two columns of the row always has a ISO Code and Description
              DataRow dr = dt.NewRow();
              dr[0] = rec.Item;
  
              // For each record
              var Reason = rec.ReturnReason.ToList();
              var Qty = rec.Qty.ToList();
  
              // Because each row may have different reasons I am indexing
              // the with the string name
              for (int i = 0; i &lt; Qty.Count; i++)
              {
                  var reasonTexts = typeof(ReturnReasons).GetProperties().Where(p =&gt; p.Name == "a" + Reason[i].ToString()).ToList()[0].GetValue(null, null).ToString();
                  dr[reasonTexts] = Qty[i].Value;
              }
  
              // Add the DataRow to the DataTable
              dt.Rows.Add(dr);
          }
  
          // Bind the DataTable to the DataGridView
          dataGridViewSummary1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing;
          dataGridViewSummary1.ColumnHeadersHeight = 200;
  
          //Connect Grid with DataSource
          //this.dataGridViewSummary1.AutoGenerateColumns = true;
          this.dataGridViewSummary1.DataSource = dt;
      }
  </code></pre>
  
  <p>The problem is, that each RetourenPivot in queryResults might have several quantities with the same ReturnReason and I don’t know how to sum them up.
  E.g. now it looks like</p>
  
  <p>Item 123
  ReturnReason {0, 0, 0, 0, 1, 16}
  Qty {1, 1, 2, 1, 5, 1}</p>
  
  <p>What means, that for the reason 0 there are 4 quantities. It should look like</p>
  
  <p>Item 123
  ReturnReason { 0, 1, 16}
  Qty {5, 5, 1}</p>