title: Combine items from different rows in database and display them in one row on listview
body: <p>I have items in a database.  The columns are ID, Region, and Report.  I display all these in an asp listview.  I need combine each of these items and display them in one row for each id.  So for example if ID = testuser  Regions = 1, 2  Reports = A, B</p>
  
  <p>Right now the list displays something like this:</p>
  
  <p>testuser | 1 | A</p>
  
  <p>testuser | 1 | B</p>
  
  <p>testuser | 2 | A</p>
  
  <p>testuser | 2 | B</p>
  
  <p>I want it to be:</p>
  
  <p>testuser | 1,2  | A,B</p>
  
  <p>Here is the front end: </p>
  
  <pre><code>&lt;ItemTemplate&gt;
          &lt;tr class="tableRow"&gt;
              &lt;td class="value"&gt;
                  &lt;asp:Label runat="server" ID="lblCWSID" Text='&lt;%# Eval("CwsId") %&gt;'&gt;&lt;/asp:Label&gt;
  
              &lt;/td&gt;
              &lt;td class="value"&gt;
                  &lt;asp:Label ID="lblRegion" runat="server" Text='&lt;%# Eval("Regions") %&gt;'&gt;&lt;/asp:Label&gt;
              &lt;/td&gt;
              &lt;td class="value" style="width: 70px"&gt;
                  &lt;asp:Label ID="lblReport" runat="server" Text='&lt;%# Eval("Reports") %&gt;'&gt;&lt;/asp:Label&gt;
              &lt;/td&gt;
              &lt;td class="value" style="width: 50px"&gt;
               &lt;asp:ImageButton ID="btnEdit" runat="server" ImageUrl="~/App_Themes/Default/Images/edit-icon.png"
                      OnCommand="btnEdit_User" CommandArgument='&lt;%# Eval("CWSID") %&gt;' Height="20px"
                      Width="20px" /&gt;
                  &lt;asp:ImageButton ID="btnDelete" runat="server" ImageUrl="~/App_Themes/Default/Images/remove-icon.png"
                      OnCommand="btnDelete_User" CommandArgument='&lt;%# Eval("CWSID") %&gt;' Height="20px"
                      Width="20px" /&gt;
              &lt;/td&gt;
          &lt;/tr&gt;
      &lt;/ItemTemplate&gt;
  </code></pre>
  
  <p>Here is the backend:</p>
  
  <pre><code>    /// &lt;summary&gt;
      /// Add a user to the list and then rebind
      /// &lt;/summary&gt;
      /// &lt;param name="sender"&gt;&lt;/param&gt;
      /// &lt;param name="e"&gt;&lt;/param&gt;
      protected void lbSaveUser_Click(object sender, CommandEventArgs e)
      {
  
  
          List&lt;string&gt; selectedRegions = RegionsCheckBox.Items.Cast&lt;ListItem&gt;()
          .Where(li =&gt; li.Selected)
          .Select(li =&gt; li.Value)
          .ToList();
  
          List&lt;string&gt; selectedReports = ReportsCheckBox.Items.Cast&lt;ListItem&gt;()
          .Where(li =&gt; li.Selected)
          .Select(li =&gt; li.Value)
          .ToList();
  
          try
          {
              if (String.IsNullOrWhiteSpace(txtCWSID.Text))
              {
                  this.lblError.Text = "Please enter a valid CWS ID";
                  return;
              }
  
              if (Common.GetUserCwsId() == txtCWSID.Text.Trim())
              {
                  this.lblError.Text = "It would not be a good idea to block yourself from the site";
                  return;
              }
  
              ReportPermissionsFactory.DeleteReportPermissionUser(txtCWSID.Text, Common.GetConnectionString());
              foreach (string Region in selectedRegions)
              {
                  foreach (string Report in selectedReports)
                  {
                      ReportPermissionsFactory.InsUpdReportPermissions(txtCWSID.Text.Trim(),Region, Report, Common.GetConnectionString());
                  }
              }
              txtCWSID.Text = String.Empty; 
  
              BindList();
          }
          catch (Exception ex)
          {
              Logger.HandleException(Common.GetUserCwsId(), Projects.GlobalSizingTool, "ReportPermissionsUsers", "lbAddUser_Click", ex, Common.GetConnectionString());
              this.lblError.Text = "There was an error completing your request.  Please try again.";
          }
      }
  </code></pre>
  
  <p>Here is how the data gets loaded:</p>
  
  <pre><code>internal static List&lt;ReportPermissions&gt; LoadData(string connectionString, bool refresh = false)
      {
          string cacheItem = "ReportPermissionsFactory";
          ObjectCache cache = MemoryCache.Default;
  
          if (refresh)
          {
              cache.Remove(cacheItem);
          }
  
          List&lt;ReportPermissions&gt; l = cache[cacheItem] as List&lt;ReportPermissions&gt;;
  
          if (l == null || l.Count == 0)
          {
              l = new List&lt;ReportPermissions&gt;();
              Database db = DatabaseFactory.CreateDatabase(connectionString);
              DbCommand wrapper = db.GetStoredProcCommand("usp_GetReportPermissionsUsers");
              IDataReader reader = db.ExecuteReader(wrapper);
  
              using (wrapper)
              {
                  using (reader)
                  {
                      while (reader.Read())
                      {
                          ReportPermissions obj = new ReportPermissions();
                          obj.CwsId= SizingDBLayer.Utilities.GetDBFieldString(reader["CwsId"]);
                          obj.Regions = SizingDBLayer.Utilities.GetDBFieldString(reader["RegionCode"]);
                          obj.Reports = SizingDBLayer.Utilities.GetDBFieldString(reader["Reports"]);
                          l.Add(obj);
                      }
                  }
              }
              cache.Add(cacheItem, l, new DateTimeOffset(DateTime.Now.AddMinutes(5)));
          }
          return l;
      }
  </code></pre>