locked
Pivot a List RRS feed

  • Question

  • User1133338985 posted

    I have a sorted list that I need to display in a gridview.

    I currently only have 3 records in it, just for testing.

    callLogs[0] Name = “John Doe” Extention = “102” Date = “1/1/2017” Weekday = “Monday” Count = “2”

    callLogs[1] Name = “John Doe” Extention = “102” Date = “1/2/2017” Weekday = “Tuesday” Count = “6”

    callLogs[2] Name = “Jane Doe” Extention = “103” Date = “1/4/2017” Weekday = “Thursday” Count = “16”

    And I need it displayed like

    Name       Extension         Monday 1/1/2017            Tuesday 1/2/2017     Wednesday 1/3/2017     Thursday 1//2017            Total

    JohnDoe  102                     2                                                              6                             0                                          0                                 8

    JaneDoe  103                     0                                                               0                             0                                         16                               16

    I am then going to put this in my dynamically created datatable.

    If someone has a better idea I am open for additional options.

    Thanks,

    Keith.

    Thursday, December 14, 2017 8:49 PM

All replies

  • User283571144 posted

    Hi kss,

    According to your description, I suggest you could use LinQ group key works to select the total value.

    Then you could use foreach to foearch the selected value and fill the data into datatable.

    More details, you could refer to below codes:

        public class callLogs
        {
            public string Name { get; set; }
            public string Extention { get; set; }
            public string Date { get; set; }
            public string Weekday { get; set; }
            public string Count { get; set; }
        }
    
        public partial class GridviewTest : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                List<callLogs> l1 = new List<callLogs>() { new callLogs { Name = "John Doe", Extention = "102",Date = "1/1/2017",Weekday = "Monday",Count = "2"},
                    new callLogs{Name = "John Doe", Extention = "102",Date = "1/2/2017",Weekday = "Tuesday",Count = "6" },
                     new callLogs{Name = "John Doe", Extention = "103",Date = "1/4/2017",Weekday = "Thursday",Count = "16" },
                };
    
    
                var re = (from c in l1
                          group c by new { c.Extention, c.Name } into calllogsgroup
                          select new
                          {
                              name = calllogsgroup.Key.Name,
                              extention = calllogsgroup.Key.Extention,
                              total = calllogsgroup.Sum(r1 => Int32.Parse(r1.Count)),
                              callLogsgroup = calllogsgroup.ToList()
                          }).ToList();
    
                DataTable d1 = new DataTable();
                d1.Columns.Add("Name");
                d1.Columns.Add("Extension");
                d1.Columns.Add("Total");
                foreach (var item in re)
                {
                    DataRow dr = d1.NewRow();
                    dr["Name"] = item.name;
                    dr["Extension"] = item.extention;
                    foreach (var childvalue in item.callLogsgroup)
                    {
                        d1.Columns.Add(childvalue.Date + childvalue.Weekday);
                        dr[childvalue.Date + childvalue.Weekday] = childvalue.Count;
                    }
                    dr["Total"] = item.total;
                    d1.Rows.Add(dr);
                }
    
                GridView1.DataSource = d1;
                GridView1.DataBind();
            }
        }

    Result:

    Best Regards,

    Brando

    Monday, December 18, 2017 7:10 AM
  • User1108898723 posted

    Alternative to LINQ code can be NReco.PivotData library which can be used for pivoting a list:

    IEnumerable<LogEntry> logEntries;
    var pvtData = new PivotData(new [] {"Name","Extention","Date","Weekday"}, new SumAggregatorFactory("Count") );
    pvtData.ProcessData( customers, new ObjectMember().GetValue );
    
    var pvtTable = new PivotTable(
      new [] {"Name", "Extension" },   // rows
      new [] {"Weekday", "Date"},  // columns
      pvtData);
    
    // now you can use pvtTable.RowKeys and pvtTable.ColumnKeys to interate pivot table cells, and use indexer to get cell values
    
    // also you can use advanced component from NReco.PivotData.Extensions nuget package and get a DataTable that can be binded to a gridview:
    
    var dataTableWr = new NReco.PivotData.Output.PivotTableDataTableWriter("Logs");
    DataTable tbl = dataTableWr.Write(pvtTbl);

    Tuesday, January 9, 2018 3:53 PM