I have records like this in observable collection
ID Department salary joingdate 1 .NET 5000 04/08/2011 2 .NET 6000 04/07/2011 3 JAVA 7000 04/08/2011 4 JAVA 8000 04/07/2011 5 .NET 9000 04/06/2011
now I want new observable collection like
Joingdate .NET(Salary) JAVA(Salary) 04/08/2011 5000 7000 04/07/2011 6000 8000 04/06/2011 9000 NULL
How would I get this type of observable collection?
Assuming you have follwing structure that resembles your structure:
public class Data1
{
public int Id { get; set; }
public String Dep { get; set; }
public int Sal { get; set; }
public String JoinDate { get; set; }
}
public class Data2
{
public Data2()
{
Sal = new List<int>();
}
public List<int> Sal { get; set; }
public String JoinDate { get; set; }
public override string ToString()
{
return Sal.Aggregate(JoinDate, (current, s) => current + s.ToString());
}
}
And following observable collections:
public class Data1List : ObservableCollection<Data1>
{
public Data1List()
{
Add(new Data1{ Id = 1, Dep = ".NET", Sal = 5000, JoinDate = "04/08/2011"});
Add(new Data1{ Id = 2, Dep = ".NET", Sal = 6000, JoinDate = "04/07/2011"});
Add(new Data1{ Id = 3, Dep = "JAVA", Sal = 7000, JoinDate = "04/08/2011"});
Add(new Data1{ Id = 4, Dep = "JAVA", Sal = 8000, JoinDate = "04/07/2011"});
Add(new Data1{ Id = 5, Dep = ".NET", Sal = 9000, JoinDate = "04/06/2011"});
}
}
public class Data2List : ObservableCollection<Data2>
{
}
You could try this code to solve your problem or at least get you on the way to a maybe better solution:
var l1 = new Data1List();
var l2 = new Data2List();
foreach (var items in l1.GroupBy(d => d.JoinDate))
{
var d2 = new Data2 { JoinDate = items.Key };
foreach (var item in items)
d2.Sal.Add(item.Sal);
l2.Add(d2);
}
I hope this helps you getting to a solution! :)
Some time ago I played around with pivoting and the ExpandoObject. This is certainly not production code.
public static dynamic pivot(IEnumerable<Employee> rows)
{
IDictionary<string, Object> expando = new ExpandoObject();
expando["Joindate"] = rows.First().Joindate;
foreach (var row in rows)
{
expando[row.Department] = row.Salary;
}
return (dynamic)expando;
}
then in some method somewhere
var employees = new ObservableCollection<Employee>() {
new Employee() {ID=1, Department="NET", Salary=5000, Joindate=new DateTime(2011,04,08)},
new Employee() {ID=2, Department="NET", Salary=6000, Joindate=new DateTime(2011,04,07)},
new Employee() {ID=3, Department="JAVA", Salary=7000, Joindate=new DateTime(2011,04,08)},
new Employee() {ID=4, Department="JAVA", Salary=8000, Joindate=new DateTime(2011,04,07)},
new Employee() {ID=5, Department="NET", Salary=9000, Joindate=new DateTime(2011,04,06)}
};
var distinctDates = employees.Select(j => j.Joindate).Distinct().OrderByDescending(d => d);
var salaryByDepartmentAndJoindate = distinctDates.Select(d => pivot(employees.Where(jd => jd.Joindate == d)));
var result = new ObservableCollection<dynamic>(salaryByDepartmentAndJoindate);
Due to how my data is consumed, I have needed the results in a table format, not a nested object format. So the code below produces a pivot from a list of data. It is called like this:
// generate a pivot table
var pivot = linqQueryResults.Pivot(
rowKey => rowKey.DepartmentName,
columnKey => columnKey.JoiningDate,
value => value.Sum(emp => emp.Salary),
"Department",
new Dictionary<string, Func<GetComplianceForClientCurriculums_Result, object>>()
{
{"DepartmentCode", extraRow => extraRow.DepartmentCode},
{"DepartmentManager", extraRow => extraRow.DeptManager}
}
);
And the LINQ extension method looks like this:
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
namespace MyApplication.Extensions
{
public static class LinqExtenions
{
/// <summary>
/// Groups the elements of a sequence according to a specified firstKey selector
/// function and rotates the unique values from the secondKey selector function into
/// multiple values in the output, and performs aggregations.
/// </summary>
/// <param name="source">The data source for the pivot</param>
/// <param name="rowKeySelector">A function to derive the key for the rows</param>
/// <param name="columnKeySelector">A function to derive the key for the columns</param>
/// <param name="valueSelector">A function to calculate the contents of the intersection element. Usually this is an aggreation function</param>
/// <param name="firstColumnName">The label to give the first column (row title)</param>
/// <param name="additionalHeaderSelectors">An optional dictionary of additional rows to use as headers. Typically, this data should be consistent with the row selector since only the first match is taken.</param>
/// <returns>A datatable pivoted from the IEnumerable source.</returns>
/// <remarks>
/// Based on concepts from this article: http://www.extensionmethod.net/Details.aspx?ID=147
/// </remarks>
public static DataTable Pivot<TSource, TRowKey, TColumnKey, TValue>(this IEnumerable<TSource> source, Func<TSource, TRowKey> rowKeySelector, Func<TSource, TColumnKey> columnKeySelector, Func<IEnumerable<TSource>, TValue> valueSelector, string firstColumnName = "", IDictionary<string, Func<TSource, object>> additionalHeaderSelectors = null)
{
var result = new DataTable();
// determine what columns the datatable needs and build out it's schema
result.Columns.Add(new DataColumn(firstColumnName));
var columnNames = source.ToLookup(columnKeySelector);
foreach (var columnName in columnNames)
{
var newColumn = new DataColumn(columnName.Key.ToString());
result.Columns.Add(newColumn);
}
// if we have a 2nd header row, add it
if (additionalHeaderSelectors != null)
{
foreach (var additionalHeaderSelector in additionalHeaderSelectors)
{
var newRow = result.NewRow();
newRow[firstColumnName] = additionalHeaderSelector.Key;
foreach (var columnName in columnNames)
{
newRow[columnName.Key.ToString()] = additionalHeaderSelector.Value(columnName.FirstOrDefault());
}
result.Rows.Add(newRow);
}
}
// build value rows
var rows = source.ToLookup(rowKeySelector);
foreach (var row in rows)
{
var newRow = result.NewRow();
// put the key into the first column
newRow[firstColumnName] = row.Key.ToString();
// get the values for each additional column
var columns = row.ToLookup(columnKeySelector);
foreach (var column in columns)
{
newRow[column.Key.ToString()] = valueSelector(column);
}
result.Rows.Add(newRow);
}
return result;
}
}
}
