Click here to Skip to main content
15,168,906 members
Home / Discussions / C#
   

C#

 
GeneralRe: C# Confusion about LINQ Grouping Pin
Richard Deeming15-Sep-21 5:15
mveRichard Deeming15-Sep-21 5:15 
AnswerRe: C# Confusion about LINQ Grouping Pin
BillWoodruff15-Sep-21 20:35
mveBillWoodruff15-Sep-21 20:35 
GeneralRe: C# Confusion about LINQ Grouping Pin
Mou_kol15-Sep-21 21:22
MemberMou_kol15-Sep-21 21:22 
GeneralRe: C# Confusion about LINQ Grouping Pin
Dave Kreskowiak16-Sep-21 3:25
mveDave Kreskowiak16-Sep-21 3:25 
GeneralRe: C# Confusion about LINQ Grouping Pin
Mou_kol16-Sep-21 7:30
MemberMou_kol16-Sep-21 7:30 
GeneralRe: C# Confusion about LINQ Grouping Pin
Mou_kol15-Sep-21 21:39
MemberMou_kol15-Sep-21 21:39 
QuestionC# How to convert my List<T> to Pivot datatable Pin
Mou_kol14-Sep-21 7:00
MemberMou_kol14-Sep-21 7:00 
AnswerRe: C# How to convert my List<T> to Pivot datatable Pin
Richard Deeming14-Sep-21 7:56
mveRichard Deeming14-Sep-21 7:56 
That method only supports grouping on a single column. Dynamic grouping on multiple columns is not trivial.

This works for me given you sample input data:
C#
public static DataTable ToPivotTable<T, TPivot>(
    this IEnumerable<T> source,
    IReadOnlyList<Expression<Func<T, object>>> groupColumns,
    Func<T, string> pivotColumn, Func<T, TPivot> pivotValue)
{
    var result = new DataTable();
    var groupColumnMembers = groupColumns.Select(c => ((MemberExpression)c.Body).Member).Cast<PropertyInfo>().ToList();
    foreach (var groupColumn in groupColumnMembers)
    {
        result.Columns.Add(new DataColumn(groupColumn.Name, groupColumn.PropertyType));
    }
    
    var p = Expression.Parameter(typeof(T), "p");
    var tupleType = Type.GetType($"System.Tuple`{groupColumns.Count}");
    var groupColumnTypes = groupColumnMembers.Select(x => x.PropertyType).ToArray();
    var tupleConstructor = tupleType.MakeGenericType(groupColumnTypes).GetConstructor(groupColumnTypes);
        
    var args = groupColumnMembers.Select(c => Expression.Property(p, c));
    var body = Expression.New(tupleConstructor, args);
    var groupByExpression = Expression.Lambda<Func<T, System.Runtime.CompilerServices.ITuple>>(body, p);
    
    foreach (var group in source.GroupBy(groupByExpression.Compile()))
    {
        foreach (T element in group)
        {
            string pivotColumnName = pivotColumn(element);
            if (!result.Columns.Contains(pivotColumnName))
            {
                result.Columns.Add(new DataColumn(pivotColumnName, typeof(TPivot)));
            }
        }
        
        var row = result.NewRow();
        for (int index = 0; index < group.Key.Length; index++)
        {
            row[index] = group.Key[index];
        }
        
        foreach (T element in group)
        {
            string pivotColumnName = pivotColumn(element);
            TPivot pivotColumnValue = pivotValue(element);
            row[pivotColumnName] = pivotColumnValue;
        }
        
        result.Rows.Add(row);
    }
    
    return result;
}
C#
DataTable pivotedData = _data.ToPivotTable(
    new List<Expression<Func<Data, object>>>
    {
        d => d.Section, 
        d => d.Lineitem, 
        d => d.BrokerCode, 
        d => d.BrokerName 
    }, 
    d => d.Period, 
    d => d.PeriodValue);
Output:
plain
Section         | Lineitem           | BrokerCode | BrokerName             | 2012 FYA | 2013 FYA | 1Q 2014A | 2Q 2014A
----------------------------------------------------------------------------------------------------------------------
Consensus Model | Net Revenue        | ZB         | B Securities           |          | null     | null     | null 
Consensus Model | Net Revenue        | ZB         | B. Riley Securities    | null     |          | 204.45   | 205.00 
Consensus Model | Net Revenue        | TU         | Cantor Fitzgerald & Co | 101.33   |          | 204.45   | 201.00 
Consensus Model | Cost of Goods Sold | ZB         | B. Riley Securities    |          |          | 204.45   | 201.00 
Consensus Model | Cost of Goods Sold | TU         | Cantor Fitzgerald & Co | 101.33   | 222.30   | 784.45   | 555.00 




"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
BillWoodruff14-Sep-21 20:29
mveBillWoodruff14-Sep-21 20:29 
GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
Mou_kol14-Sep-21 22:05
MemberMou_kol14-Sep-21 22:05 
GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
Mou_kol14-Sep-21 23:50
MemberMou_kol14-Sep-21 23:50 
GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
Richard Deeming15-Sep-21 0:15
mveRichard Deeming15-Sep-21 0:15 
GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
Mou_kol15-Sep-21 0:54
MemberMou_kol15-Sep-21 0:54 
GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
Richard Deeming15-Sep-21 1:38
mveRichard Deeming15-Sep-21 1:38 
GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
Mou_kol15-Sep-21 4:49
MemberMou_kol15-Sep-21 4:49 
GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
Richard Deeming15-Sep-21 5:13
mveRichard Deeming15-Sep-21 5:13 
GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
Mou_kol15-Sep-21 21:37
MemberMou_kol15-Sep-21 21:37 
GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
Pete O'Hanlon15-Sep-21 7:52
mvaPete O'Hanlon15-Sep-21 7:52 
GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
BillWoodruff15-Sep-21 9:52
mveBillWoodruff15-Sep-21 9:52 
GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
Mou_kol15-Sep-21 21:20
MemberMou_kol15-Sep-21 21:20 
QuestionHow to store Pivot data into List<T> Pin
Mou_kol14-Sep-21 6:53
MemberMou_kol14-Sep-21 6:53 
AnswerRe: How to store Pivot data into List<T> Pin
Richard Deeming14-Sep-21 7:04
mveRichard Deeming14-Sep-21 7:04 
GeneralRe: How to store Pivot data into List<T> Pin
Mou_kol14-Sep-21 7:29
MemberMou_kol14-Sep-21 7:29 
GeneralRe: How to store Pivot data into List<T> Pin
Richard Deeming14-Sep-21 7:49
mveRichard Deeming14-Sep-21 7:49 
GeneralRe: How to store Pivot data into List<T> Pin
Mou_kol15-Sep-21 0:14
MemberMou_kol15-Sep-21 0:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.