Click here to Skip to main content
15,868,005 members
Home / Discussions / C#
   

C#

 
GeneralRe: C# Confusion about LINQ Grouping Pin
Richard Deeming15-Sep-21 4:15
mveRichard Deeming15-Sep-21 4:15 
AnswerRe: C# Confusion about LINQ Grouping Pin
BillWoodruff15-Sep-21 19:35
professionalBillWoodruff15-Sep-21 19:35 
GeneralRe: C# Confusion about LINQ Grouping Pin
Mou_kol15-Sep-21 20:22
Mou_kol15-Sep-21 20:22 
GeneralRe: C# Confusion about LINQ Grouping Pin
Dave Kreskowiak16-Sep-21 2:25
mveDave Kreskowiak16-Sep-21 2:25 
GeneralRe: C# Confusion about LINQ Grouping Pin
Mou_kol16-Sep-21 6:30
Mou_kol16-Sep-21 6:30 
GeneralRe: C# Confusion about LINQ Grouping Pin
Mou_kol15-Sep-21 20:39
Mou_kol15-Sep-21 20:39 
QuestionC# How to convert my List<T> to Pivot datatable Pin
Mou_kol14-Sep-21 6:00
Mou_kol14-Sep-21 6:00 
AnswerRe: C# How to convert my List<T> to Pivot datatable Pin
Richard Deeming14-Sep-21 6:56
mveRichard Deeming14-Sep-21 6: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 19:29
professionalBillWoodruff14-Sep-21 19:29 
GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
Mou_kol14-Sep-21 21:05
Mou_kol14-Sep-21 21:05 
GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
Mou_kol14-Sep-21 22:50
Mou_kol14-Sep-21 22:50 
GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
Richard Deeming14-Sep-21 23:15
mveRichard Deeming14-Sep-21 23:15 
GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
Mou_kol14-Sep-21 23:54
Mou_kol14-Sep-21 23:54 
GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
Richard Deeming15-Sep-21 0:38
mveRichard Deeming15-Sep-21 0:38 
GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
Mou_kol15-Sep-21 3:49
Mou_kol15-Sep-21 3:49 
GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
Richard Deeming15-Sep-21 4:13
mveRichard Deeming15-Sep-21 4:13 
GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
Mou_kol15-Sep-21 20:37
Mou_kol15-Sep-21 20:37 
GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
Pete O'Hanlon15-Sep-21 6:52
subeditorPete O'Hanlon15-Sep-21 6:52 
GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
BillWoodruff15-Sep-21 8:52
professionalBillWoodruff15-Sep-21 8:52 
GeneralRe: C# How to convert my List<T> to Pivot datatable Pin
Mou_kol15-Sep-21 20:20
Mou_kol15-Sep-21 20:20 
QuestionHow to store Pivot data into List<T> Pin
Mou_kol14-Sep-21 5:53
Mou_kol14-Sep-21 5:53 
AnswerRe: How to store Pivot data into List<T> Pin
Richard Deeming14-Sep-21 6:04
mveRichard Deeming14-Sep-21 6:04 
GeneralRe: How to store Pivot data into List<T> Pin
Mou_kol14-Sep-21 6:29
Mou_kol14-Sep-21 6:29 
GeneralRe: How to store Pivot data into List<T> Pin
Richard Deeming14-Sep-21 6:49
mveRichard Deeming14-Sep-21 6:49 
GeneralRe: How to store Pivot data into List<T> Pin
Mou_kol14-Sep-21 23:14
Mou_kol14-Sep-21 23: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.