none
How to show my List of data in Pivot format RRS feed

  • Question

  • I have list of information with following fields:

    Project    State         Title
      ABC      Resolved      Title1
      ABC      Pending       Title2
      DEF      Archived      Title3
      DEF      Resolved      Title4
      DEF      Committed     Title5
      DEF      Active        Title6

    I want output in following format using c#

    Project    Pending      Resolved       Committed     Active
      ABC         2            1              0            3
      DEF         1            3              1            15

    My full code

    private void button2_Click(object sender, EventArgs e)
    {
    	List<Item> objItems = new List<Item>();
    
    	objItems.Add(new Item(1, "ABC", "Title1", "Resolved"));
    	objItems.Add(new Item(2, "ABC", "Title2", "Pending"));
    	objItems.Add(new Item(3, "DEF", "Title3", "Archived"));
    	objItems.Add(new Item(4, "DEF", "Title4", "Resolved"));
    	objItems.Add(new Item(5, "DEF", "Title5", "Committed"));
    	objItems.Add(new Item(6, "DEF", "Title6", "Active"));
    	objItems.Add(new Item(7, "ABC", "Title2", "Pending"));
    
    	var data = objItems.Pivot(c => c.Project, c => c.State, lst => lst.Sum(c=> c.State)).ToList();
    	dataGridView1.DataSource = data;  
    }
    
    public class Item
    {
    	public int Id { get; set; }
    	public string Project { get; set; }
    	public string Title { get; set; }
    	public string State { get; set; }
    
    	public Item(int id, string project, string title, string state)
    	{
    		this.Id = id;
    		this.Project = project;
    		this.Title = title;
    		this.State = state;
    	}
    }
    
        public static class Extension
        {
    
            public static DataTable ToPivotTable<T, TColumn, TRow, TData>(
                this IEnumerable<T> source,
                Func<T, TColumn> columnSelector,
                Expression<Func<T, TRow>> rowSelector,
                Func<IEnumerable<T>, TData> dataSelector)
            {
                DataTable table = new DataTable();
                var rowName = ((MemberExpression)rowSelector.Body).Member.Name;
                table.Columns.Add(new DataColumn(rowName));
                var columns = source.Select(columnSelector).Distinct();
    
                foreach (var column in columns)
                    table.Columns.Add(new DataColumn(column.ToString()));
    
                var rows = source.GroupBy(rowSelector.Compile())
                                 .Select(rowGroup => new
                                 {
                                     Key = rowGroup.Key,
                                     Values = columns.GroupJoin(
                                         rowGroup,
                                         c => c,
                                         r => columnSelector(r),
                                         (c, columnGroup) => dataSelector(columnGroup))
                                 });
    
                foreach (var row in rows)
                {
                    var dataRow = table.NewRow();
                    var items = row.Values.Cast<object>().ToList();
                    items.Insert(0, row.Key);
                    dataRow.ItemArray = items.ToArray();
                    table.Rows.Add(dataRow);
                }
    
                return table;
            }
    
        public static Dictionary<TKey1, Dictionary<TKey2, TValue>> Pivot<TSource, TKey1, TKey2, TValue>(
        this IEnumerable<TSource> source
        , Func<TSource, TKey1> key1Selector
        , Func<TSource, TKey2> key2Selector
        , Func<IEnumerable<TSource>, TValue> aggregate)
            {
                return source.GroupBy(key1Selector).Select(
                    x => new
                    {
                        X = x.Key,
                        Y = source.GroupBy(key2Selector).Select(
                            z => new
                            {
                                Z = z.Key,
                                V = aggregate(from item in source
                                              where key1Selector(item).Equals(x.Key)
                                              && key2Selector(item).Equals(z.Key)
                                              select item
                                )
    
                            }
                        ).ToDictionary(e => e.Z, o => o.V)
                    }
                ).ToDictionary(e => e.X, o => o.Y);
            } 
        }

    This Pivot code i got from a site https://stackoverflow.com/a/6282079

    public static Dictionary<TKey1, Dictionary<TKey2, TValue>> Pivot<TSource, TKey1, TKey2, TValue>()

    Tell me how to arrange my data using my above Pivot function. please rectify my code to get desire output. thanks

    • Moved by CoolDadTx Tuesday, November 12, 2019 2:49 PM Winforms related
    Tuesday, November 12, 2019 9:02 AM

All replies

  • Hi Sudip_inn ,

    I can't find the relationship between the two tables you listed above.

    For example, why the value of ABC's pending is 2 and the value of DEF’s committed is 15?

    Could you please explain it in detail?

    Best Regards,

    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, November 13, 2019 7:08 AM
  • i am showing 2 1 etc that is count of project

    Project    Pending      Resolved       Committed     Active
      ABC         2            1              0            3
      DEF         1            3              1            15

    ABC has 2 pending, 1 resolve

    DEF has 1 pending 3 resolve

    please rectify my code as a result i should be able to arrange data with pivot function. thanks

    Wednesday, November 13, 2019 8:06 PM
  • Hi Sudip_inn,

    To output the data in the form you described, I get the count of states by the statements if and switch case. Then store it in the new list.

    Here is my code you can refer to.

    private void Button2_Click(object sender, EventArgs e)
    {
                for (int i = 0; i < objItems.Count; i++)
                {
                    String s = objItems[i].State;
                    if (objItems[i].Project == "ABC")
                    {
                        switch (s) {
                            case "Pending":
                                ap++;
                                break;
                            case "Resolved":
                                ar++;
                                break;
                            case "Active":
                                aa++;
                                break;
                            case "Committed":
                                ac++;
                                break;
                        }
                    }
                    if (objItems[i].Project == "DEF")
                    {
                        switch (s)
                        {
                            case "Pending":
                                dp++;
                                break;
                            case "Resolved":
                                dr++;
                                break;
                            case "Active":
                                da++;
                                break;
                            case "Committed":
                                dc++;
                                break;
                        }
                    }
                }
                list.Add(new Item1(1, "ABC", ap, ar, ac, aa));
                list.Add(new Item1(2, "DEF", dp, dr, dc, da));
                dataGridView1.DataSource = list;
    }

    Best Regards,

    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 15, 2019 3:17 AM