C# How to show my List of data in Pivot format

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

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 here https://stackoverflow.com/a/6282079

public static Dictionary> Pivot()

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

score 0 · Answer 1 · answered Nov 13 '19 at 20:58

To be honest, you'd probably be better off using the System.Linq namespace and utilizing something like the GroupBy() method and select counts of specific statuses into a new list of class type (conforming to the desired layout), or even into an anonymous object.

Once the data is grouped, you can then loop and output accordingly.

Apologies for lack of examples, am on mobile with limited battery, hope this at least points you in the direction of an easier solution :)

C# How to show my List of data in Pivot format

1 Answers1