How to show my List of data in Pivot format

[Sudip_inn]

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 Pivot data using LINQ

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

Continue reading...