将数据库中不同行的项目合并，并在列表视图中显示在一行中

我在数据库中有项目。列为ID、Region和Report。我在asp列表视图中显示所有这些。我需要将这些项目中的每一个组合起来，并将它们显示在每个id的一行中。因此，例如，如果id=testuser Regions=1，2 Reports=A，B

现在列表显示如下：

测试用户|1|

测试用户|1|B

测试用户|2|

测试用户|2|B

我希望它是：

测试用户|1,2|A，B

这是前端：

<ItemTemplate>
        <tr class="tableRow">
            <td class="value">
                <asp:Label runat="server" ID="lblCWSID" Text='<%# Eval("CwsId") %>'></asp:Label>
            </td>
            <td class="value">
                <asp:Label ID="lblRegion" runat="server" Text='<%# Eval("Regions") %>'></asp:Label>
            </td>
            <td class="value" style="width: 70px">
                <asp:Label ID="lblReport" runat="server" Text='<%# Eval("Reports") %>'></asp:Label>
            </td>
            <td class="value" style="width: 50px">
             <asp:ImageButton ID="btnEdit" runat="server" ImageUrl="~/App_Themes/Default/Images/edit-icon.png"
                    OnCommand="btnEdit_User" CommandArgument='<%# Eval("CWSID") %>' Height="20px"
                    Width="20px" />
                <asp:ImageButton ID="btnDelete" runat="server" ImageUrl="~/App_Themes/Default/Images/remove-icon.png"
                    OnCommand="btnDelete_User" CommandArgument='<%# Eval("CWSID") %>' Height="20px"
                    Width="20px" />
            </td>
        </tr>
    </ItemTemplate>

这是后端：

    /// <summary>
    /// Add a user to the list and then rebind
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void lbSaveUser_Click(object sender, CommandEventArgs e)
    {

        List<string> selectedRegions = RegionsCheckBox.Items.Cast<ListItem>()
        .Where(li => li.Selected)
        .Select(li => li.Value)
        .ToList();
        List<string> selectedReports = ReportsCheckBox.Items.Cast<ListItem>()
        .Where(li => li.Selected)
        .Select(li => 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.";
        }
    }

以下是数据的加载方式：

internal static List<ReportPermissions> LoadData(string connectionString, bool refresh = false)
    {
        string cacheItem = "ReportPermissionsFactory";
        ObjectCache cache = MemoryCache.Default;
        if (refresh)
        {
            cache.Remove(cacheItem);
        }
        List<ReportPermissions> l = cache[cacheItem] as List<ReportPermissions>;
        if (l == null || l.Count == 0)
        {
            l = new List<ReportPermissions>();
            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;
    }

public class ReportPermissionsById
{
    public int CwsId { get; set; }
    public string Regions { get; set; }
    public string Reports { get; set; }
}
public void BindList()
{
    var list = LoadData(connString);
    var reportPermissionsById = list
       .GroupBy(r => r.CwsId)
       .Select(r =>
            new ReportPermissionsById
            {
                 CwsId = r.Key,
                 Regions = string.Join(",", r.Select(d => d.Regions)),
                 Reports = string.Join(",", r.Select(d => d.Reports)),
            }
        );
   //Use reportPermissionsById to bind your ListView
}

 List<ReportPermissions> finalizedItems = new List<ReportPermissions>();
            foreach (ReportPermissions rp in l)
            {
                //Check to see if record for this user exists
                if (!finalizedItems.Any(x => x.CwsId == rp.CwsId))
                {
                    // if it doesn't exist, get it
                    ReportPermissions perm = new ReportPermissions();
                    perm.CwsId = rp.CwsId;
                    perm.Reports = string.Join(",", l.Where(x => x.CwsId == rp.CwsId).Select(x => x.Reports).Distinct());
                    perm.Regions = string.Join(",", l.Where(x => x.CwsId == rp.CwsId).Select(x => x.Regions).Distinct());
                    finalizedItems.Add(perm);
                }
            }
            l= finalizedItems;