Pivot Data Using Linq

Is it possible to Pivot data using LINQ?

Something like this?

List<CustData> myList = GetCustData();

var query = myList
.GroupBy(c => c.CustId)
.Select(g => new {
CustId = g.Key,
Jan = g.Where(c => c.OrderDate.Month == 1).Sum(c => c.Qty),
Feb = g.Where(c => c.OrderDate.Month == 2).Sum(c => c.Qty),
March = g.Where(c => c.OrderDate.Month == 3).Sum(c => c.Qty)
});

GroupBy in Linq does not work the same as SQL. In SQL, you get the key and aggregates (row/column shape). In Linq, you get the key and any elements as children of the key (hierarchical shape). To pivot, you must project the hierarchy back into a row/column form of your choosing.

Pivot Table Using Linq

Try this:

//static headers version
var qry = Visits.GroupBy(v=>new{v.VisitDate, v.PersonelId})
.Select(g=>new{
VisitDate = g.Key.VisitDate,
PersonelId = g.Key.PersonelId,
A = g.Where(d=>d.VisitTypeId=="A").Count(),
B = g.Where(d=>d.VisitTypeId=="B").Count(),
D = g.Where(d=>d.VisitTypeId=="D").Count(),
S = g.Where(d=>d.VisitTypeId=="S").Count()
});

//dynamic headers version
var qry = Visits.GroupBy(v=>new{v.VisitDate, v.PersonelId})
.Select(g=>new{
VisitDate = g.Key.VisitDate,
PersonelId = g.Key.PersonelId,
subject = g.GroupBy(f => f.VisitTypeId)
.Select(m => new { Sub = m.Key, Score = m.Count()})
});

Pivot data using LINQ

I'm not saying it is a great way to pivot - but it is a pivot...

    // sample data
var data = new[] {
new { Foo = 1, Bar = "Don Smith"},
new { Foo = 1, Bar = "Mike Jones"},
new { Foo = 1, Bar = "James Ray"},
new { Foo = 2, Bar = "Tom Rizzo"},
new { Foo = 2, Bar = "Alex Homes"},
new { Foo = 3, Bar = "Andy Bates"},
};
// group into columns, and select the rows per column
var grps = from d in data
group d by d.Foo
into grp
select new {
Foo = grp.Key,
Bars = grp.Select(d2 => d2.Bar).ToArray()
};

// find the total number of (data) rows
int rows = grps.Max(grp => grp.Bars.Length);

// output columns
foreach (var grp in grps) {
Console.Write(grp.Foo + "\t");
}
Console.WriteLine();
// output data
for (int i = 0; i < rows; i++) {
foreach (var grp in grps) {
Console.Write((i < grp.Bars.Length ? grp.Bars[i] : null) + "\t");
}
Console.WriteLine();
}

Trying to pivot data using linq

If I understand the question, you want a mapping of Task to State for each Robot. You could group by Robot and select a dictionary for each group:

Logs.GroupBy(t => t.Robot).Select(g => new {
Robot = g.Key,
TaskStates = g.ToDictionary(t => t.Task, t => t.State)
})

This assumes that task names are unique for each robot (ToDictionary would throw an exception otherwise).

You could also add another level of grouping for dates:

Logs.GroupBy(t => t.LogDate).Select(g => new {
Date = g.Key,
Details = g.GroupBy(t => t.Robot).Select(g => new {
Robot = g.Key,
TaskStates = g.ToDictionary(t => t.Task, t => t.State)
}).ToList()
})

Note that the Details property is essentially equivalent to my first example, the only difference being that it queries the outer grouping instead of the whole sequence. The result is a sequence of {Date, Details} where each "detail" is a list of {Robot, TaskStates}.

I haven't tested this, so let me know if there are any bugs I missed.

How to pivot a list in C#

If you know how many props you have beforehand, you could do something like this

var query = myList
.GroupBy(c => c.TimeStamp)
.Select(g => new {
TimeStamp = g.Key,
Prop1 = g.Where(x => x.Name == "Prop1").Sum(x => x.Value),
Prop2 = g.Where(x => x.Name == "Prop2").Sum(x => x.Value),
Prop3 = g.Where(x => x.Name == "Prop3").Sum(x => x.Value),
Value = g.Sum(c => c.Value)
});

If you this list of props is dynamic, you are going to have to call pivot at the server and query this manually. or use a sublist and groupby Name

C# Linq Pivot data with variable columns

Was able to solve this using a dynamic list and the ExpandoObject class.
Here's the code, hope it helps someone else:

  List<dynamic> dynList = new List<dynamic>();
for (int i = 0; i < d.Count; i++)
{
dynamic dynObj = new ExpandoObject();
((IDictionary<string, object>)dynObj).Add("FileTypeCode", result[i].FileTypeCode.ToString());
((IDictionary<string, object>)dynObj).Add("Fullname", result[i].Fullname.ToString());
((IDictionary<string, object>)dynObj).Add("Entitlement", result[i].Entitlement.ToString());

var res = d[i].ColumnName.ToList();
for (int j = 0; j < res.Count; j++)
{
((IDictionary<string, object>)dynObj).Add(res[j].Col.ToString(), res[j].Value.ToString());
}

dynList.Add(dynObj);
}
var dList = (from da in dynList select da).ToList();

return dList;

How to pivot data in LINQ without hard coding columns

I've simplified the solution for you by modifying the details from the link.

First of all create an extension method ToPivotTable in a static class:

public static class PivotClass
{
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;
}
}

Then, use this extension method on the list to get your pivot DataTable as mentioned below:

List<CodeItemsRule> _CodeItemRules = new List<CodeItemsRule>()
{
new CodeItemsRule()
{
CodeID="00009",
ItemID="D1",
RuleID=2
},new CodeItemsRule()
{
CodeID="00009",
ItemID="D2",
RuleID=2
},new CodeItemsRule()
{
CodeID="00009",
ItemID="D3",
RuleID=1
},new CodeItemsRule()
{
CodeID="00008",
ItemID="D1",
RuleID=3
},new CodeItemsRule()
{
CodeID="00007",
ItemID="D3",
RuleID=1
},new CodeItemsRule()
{
CodeID="00007",
ItemID="D4",
RuleID=1
},new CodeItemsRule()
{
CodeID="00010",
ItemID="D3",
RuleID=2
},new CodeItemsRule()
{
CodeID="00010",
ItemID="D1",
RuleID=1
},new CodeItemsRule()
{
CodeID="00010",
ItemID="D2",
RuleID=1
}

};
var pivotTable = _CodeItemRules.ToPivotTable(
item => item.ItemID,
item => item.CodeID,
items => items.Any() ? items.Sum(x => x.RuleID) : 0);

Dynamically Pivot in LINQ: Pivoted Columns Determined at Runtime

You can build linq expression trees dynamically. This topic is covered (including example) in the following MSDN article: http://msdn.microsoft.com/en-us/library/bb882637.aspx

My suggestion would be to write an example Linq query for your task and rebuild it programmatically with expression trees. Once it works you adapt it and inject your dynamic parts.



Related Topics



Leave a reply



Submit