I am wondering if it is possible to use LINQ to pivot data from the following layout:
CustID | OrderDate | Qty
1 | 1/1/2008 | 100
2 | 1/2/2008 | 200
1 | 2/2/2008 | 350
2 | 2/28/2008 | 221
1 | 3/12/2008 | 250
2 | 3/15/2008 | 2150
into something like this:
CustID | Jan- 2008 | Feb- 2008 | Mar - 2008 |
1 | 100 | 350 | 250
2 | 200 | 221 | 2150
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.
This is most efficient way:
Check the following approach. Instead of iterating through the customers group each time for each month.
var query = myList
.GroupBy(c => c.CustId)
.Select(g => {
var results = new CustomerStatistics();
foreach (var customer in g)
{
switch (customer.OrderDate.Month)
{
case 1:
results.Jan += customer.Qty;
break;
case 2:
results.Feb += customer.Qty;
break;
case 3:
results.March += customer.Qty;
break;
default:
break;
}
}
return new
{
CustId = g.Key,
results.Jan,
results.Feb,
results.March
};
});
Or this one :
var query = myList
.GroupBy(c => c.CustId)
.Select(g => {
var results = g.Aggregate(new CustomerStatistics(), (result, customer) => result.Accumulate(customer), customerStatistics => customerStatistics.Compute());
return new
{
CustId = g.Key,
results.Jan,
results.Feb,
results.March
};
});
Complete solution:
using System;
using System.Collections.Generic;
using System.Linq;
namespace ConsoleApp
{
internal class Program
{
private static void Main(string[] args)
{
IEnumerable<CustData> myList = GetCustData().Take(100);
var query = myList
.GroupBy(c => c.CustId)
.Select(g =>
{
CustomerStatistics results = g.Aggregate(new CustomerStatistics(), (result, customer) => result.Accumulate(customer), customerStatistics => customerStatistics.Compute());
return new
{
CustId = g.Key,
results.Jan,
results.Feb,
results.March
};
});
Console.ReadKey();
}
private static IEnumerable<CustData> GetCustData()
{
Random random = new Random();
int custId = 0;
while (true)
{
custId++;
yield return new CustData { CustId = custId, OrderDate = new DateTime(2018, random.Next(1, 4), 1), Qty = random.Next(1, 50) };
}
}
}
public class CustData
{
public int CustId { get; set; }
public DateTime OrderDate { get; set; }
public int Qty { get; set; }
}
public class CustomerStatistics
{
public int Jan { get; set; }
public int Feb { get; set; }
public int March { get; set; }
internal CustomerStatistics Accumulate(CustData customer)
{
switch (customer.OrderDate.Month)
{
case 1:
Jan += customer.Qty;
break;
case 2:
Feb += customer.Qty;
break;
case 3:
March += customer.Qty;
break;
default:
break;
}
return this;
}
public CustomerStatistics Compute()
{
return this;
}
}
}
// LINQPad Code for Amy B answer
void Main()
{
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),
//April = g.Where(c => c.OrderDate.Month == 4).Sum(c => c.Qty),
//May = g.Where(c => c.OrderDate.Month == 5).Sum(c => c.Qty),
//June = g.Where(c => c.OrderDate.Month == 6).Sum(c => c.Qty),
//July = g.Where(c => c.OrderDate.Month == 7).Sum(c => c.Qty),
//August = g.Where(c => c.OrderDate.Month == 8).Sum(c => c.Qty),
//September = g.Where(c => c.OrderDate.Month == 9).Sum(c => c.Qty),
//October = g.Where(c => c.OrderDate.Month == 10).Sum(c => c.Qty),
//November = g.Where(c => c.OrderDate.Month == 11).Sum(c => c.Qty),
//December = g.Where(c => c.OrderDate.Month == 12).Sum(c => c.Qty)
});
query.Dump();
}
/// <summary>
/// --------------------------------
/// CustID | OrderDate | Qty
/// --------------------------------
/// 1 | 1 / 1 / 2008 | 100
/// 2 | 1 / 2 / 2008 | 200
/// 1 | 2 / 2 / 2008 | 350
/// 2 | 2 / 28 / 2008 | 221
/// 1 | 3 / 12 / 2008 | 250
/// 2 | 3 / 15 / 2008 | 2150
/// </ summary>
public List<CustData> GetCustData()
{
List<CustData> custData = new List<CustData>
{
new CustData
{
CustId = 1,
OrderDate = new DateTime(2008, 1, 1),
Qty = 100
},
new CustData
{
CustId = 2,
OrderDate = new DateTime(2008, 1, 2),
Qty = 200
},
new CustData
{
CustId = 1,
OrderDate = new DateTime(2008, 2, 2),
Qty = 350
},
new CustData
{
CustId = 2,
OrderDate = new DateTime(2008, 2, 28),
Qty = 221
},
new CustData
{
CustId = 1,
OrderDate = new DateTime(2008, 3, 12),
Qty = 250
},
new CustData
{
CustId = 2,
OrderDate = new DateTime(2008, 3, 15),
Qty = 2150
},
};
return custData;
}
public class CustData
{
public int CustId;
public DateTime OrderDate;
public uint Qty;
}
Group your data on month, and then project it into a new datatable with columns for each month. The new table would be your pivot table.