C# How to convert my List<T> to Pivot datatable - C# Discussion Boards

Mou_kol15-Sep-21 3:51

15-Sep-21 3:51

Sir can you please provide me any good example which use the same kind of grouping. i search google but found not any similar LINQ grouping example code.

Thanks

Re: C# Confusion about LINQ Grouping

Richard Deeming15-Sep-21 4:15

Richard Deeming

15-Sep-21 4:15

Read the documentation! D'Oh! | :doh:

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

Re: C# Confusion about LINQ Grouping

BillWoodruff15-Sep-21 19:35

BillWoodruff

15-Sep-21 19:35

Stop trying to get other people to write your code for you, and start studying and experimenting.

«The mind is not a vessel to be filled but a fire to be kindled» Plutarch

Re: C# Confusion about LINQ Grouping

Mou_kol15-Sep-21 20:22

Mou_kol

15-Sep-21 20:22

What is the meaning of forum....it is place where people discuss their problem and other give suggestion & answer if possible.

when a person do not know the answer or have negative mind or sluggish then those people come with excuse like this one.... "Stop trying to get other people to write your code for you, and start studying and experimenting."

Cheers.

Re: C# Confusion about LINQ Grouping

Dave Kreskowiak16-Sep-21 2:25

Dave Kreskowiak

16-Sep-21 2:25

It is for discussion, but what you're doing is NOT discussion. You keep saying "write more code for me."

Asking questions is a skill
CodeProject Forum Guidelines
Google: C# How to debug code
Seriously, go read these articles.

Dave Kreskowiak

Re: C# Confusion about LINQ Grouping

Mou_kol16-Sep-21 6:30

Mou_kol

16-Sep-21 6:30

Sorry sir if my approach was wrong. cheers!

Re: C# Confusion about LINQ Grouping

Mou_kol15-Sep-21 20:39

Mou_kol

15-Sep-21 20:39

i am not forcing anyone with gun to write down code for me. so anyone can overlook any post....am i right ?

cheers.

C# How to convert my List<T> to Pivot datatable

Mou_kol14-Sep-21 6:00

Mou_kol

14-Sep-21 6:00

I like to know how to convert List<t> to Pivot datatable.

this is my sample data populated in List.

List<Data> _data = new List<Data> 
    { 
        new Data
        {
            Section = "Consensus Model",
            Lineitem = "Net Revenue",
            BrokerCode = "ZB",
            BrokerName = "B Securities",
            Period = "2012 FYA",
            PeriodValue = ""
        },
        new Data
        {
            Section = "Consensus Model",
            Lineitem = "Net Revenue",
            BrokerCode = "ZB",
            BrokerName = "B. Riley Securities",
            Period = "2013 FYA",
            PeriodValue = ""
        },
        new Data
        {
            Section = "Consensus Model",
            Lineitem = "Net Revenue",
            BrokerCode = "ZB",
            BrokerName = "B. Riley Securities",
            Period = "1Q 2014A",
            PeriodValue = "204.45"
        },
        new Data
        {
            Section = "Consensus Model",
            Lineitem = "Net Revenue",
            BrokerCode = "ZB",
            BrokerName = "B. Riley Securities",
            Period = "2Q 2014A",
            PeriodValue = "205.00"
        },
        new Data
        {
            Section = "Consensus Model",
            Lineitem = "Net Revenue",
            BrokerCode = "TU",
            BrokerName = "Cantor Fitzgerald & Co",
            Period = "2012 FYA",
            PeriodValue = "101.33"
        },
        new Data
        {
            Section = "Consensus Model",
            Lineitem = "Net Revenue",
            BrokerCode = "TU",
            BrokerName = "Cantor Fitzgerald & Co",
            Period = "2013 FYA",
            PeriodValue = ""
        },
        new Data
        {
            Section = "Consensus Model",
            Lineitem = "Net Revenue",
            BrokerCode = "TU",
            BrokerName = "Cantor Fitzgerald & Co",
            Period = "1Q 2014A",
            PeriodValue = "204.45"
        },
        new Data
        {
            Section = "Consensus Model",
            Lineitem = "Net Revenue",
            BrokerCode = "TU",
            BrokerName = "Cantor Fitzgerald & Co",
            Period = "2Q 2014A",
            PeriodValue = "201.00"
        },
        new Data
        {
            Section = "Consensus Model",
            Lineitem = "Cost of Goods Sold",
            BrokerCode = "ZB",
            BrokerName = "B. Riley Securities",
            Period = "2012 FYA",
            PeriodValue = ""
        },
        new Data
        {
            Section = "Consensus Model",
            Lineitem = "Cost of Goods Sold",
            BrokerCode = "ZB",
            BrokerName = "B. Riley Securities",
            Period = "2013 FYA",
            PeriodValue = ""
        },
        new Data
        {
            Section = "Consensus Model",
            Lineitem = "Cost of Goods Sold",
            BrokerCode = "ZB",
            BrokerName = "B. Riley Securities",
            Period = "1Q 2014A",
            PeriodValue = "204.45"
        },
        new Data
        {
            Section = "Consensus Model",
            Lineitem = "Cost of Goods Sold",
            BrokerCode = "ZB",
            BrokerName = "B. Riley Securities",
            Period = "2Q 2014A",
            PeriodValue = "201.00"
        },
    
        new Data
        {
            Section = "Consensus Model",
            Lineitem = "Cost of Goods Sold",
            BrokerCode = "TU",
            BrokerName = "Cantor Fitzgerald & Co",
            Period = "2012 FYA",
            PeriodValue = "101.33"
        },
        new Data
        {
            Section = "Consensus Model",
            Lineitem = "Cost of Goods Sold",
            BrokerCode = "TU",
            BrokerName = "Cantor Fitzgerald & Co",
            Period = "2013 FYA",
            PeriodValue = "222.30"
        },
        new Data
        {
            Section = "Consensus Model",
            Lineitem = "Cost of Goods Sold",
            BrokerCode = "TU",
            BrokerName = "Cantor Fitzgerald & Co",
            Period = "1Q 2014A",
            PeriodValue = "784.45"
        },
        new Data
        {
            Section = "Consensus Model",
            Lineitem = "Cost of Goods Sold",
            BrokerCode = "TU",
            BrokerName = "Cantor Fitzgerald & Co",
            Period = "2Q 2014A",
            PeriodValue = "555.00"
        },
    };

Data grouping should be like Section, Lineitem, BrokerCode & Period
sample periods are like 2012 FYA or 2013 FYA...2Q 2014A

Desired output would be like below example

+-----------------+--------------------+-------------+-------------------+----------+----------+-----------+----------+
    |     Section     |      LineItem      | Broker Code |    Broker Name    | 2012 FYA | 2013 FYA | 1Q 2014A  | 2Q 2014A |
    +-----------------+--------------------+-------------+-------------------+----------+----------+-----------+----------+
    | Consensus Model | Net Revenue        | ZB          | B Securities      |          |          |    204.45 |   205.00 |
    | Consensus Model | Net Revenue        | TU          | Cantor Fitzgerald |   101.33 |          |    204.45 |   201.00 |
    | Consensus Model | Cost of Goods Sold | ZB          | B Securities      |          |          |    204.45 |   205.00 |
    | Consensus Model | Cost of Goods Sold | TU          | Cantor Fitzgerald |   101.33 |          |    204.45 |   201.00 |
    +-----------------+--------------------+-------------+-------------------+----------+----------+-----------+----------+

ToPivotTable() function sample code

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;
            }

Please guide me how to customize the code in the function ToPivotTable()

As a result i can use that function to get desired output.

if ToPivotTable() function code customization not possible then also tell me how to group data using LINQ to display data as i mention in the post.

Thanks

Re: C# How to convert my List<T> to Pivot datatable

Richard Deeming14-Sep-21 6:56

Richard Deeming

14-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:

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;
}

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

Re: C# How to convert my List<T> to Pivot datatable

BillWoodruff14-Sep-21 19:29

BillWoodruff

14-Sep-21 19:29

Wow !

«The mind is not a vessel to be filled but a fire to be kindled» Plutarch

Re: C# How to convert my List<T> to Pivot datatable

Mou_kol14-Sep-21 21:05

Mou_kol

14-Sep-21 21:05

Sir thank you so much. i will test your code and let you know if i face any problem.

Re: C# How to convert my List<T> to Pivot datatable

Mou_kol14-Sep-21 22:50

Mou_kol

14-Sep-21 22:50

Sir i am using VS2013 and .net version 4.5.2.

so when i copy your code in your project then i am getting error for these below line which may work in .net upper version.

var tupleType = Type.GetType($"System.Tuple`{groupColumns.Count}");
 var groupByExpression = Expression.Lambda<Func<T, System.Runtime.CompilerServices.ITuple>>(body, p);
 
 foreach (var group in source.GroupBy(groupByExpression.Compile()))
 {
 }

sir can you please provide a code which may compile and work in .net version 4.5.2 using VS2013.

thank you.

Re: C# How to convert my List<T> to Pivot datatable

Richard Deeming14-Sep-21 23:15

Richard Deeming

14-Sep-21 23:15

ITuple was added in .NET 4.7.1; you will need to upgrade your project to at least that version to use it.

NB: .NET Framework 4.5.2 will reach end of support next April:
.NET Framework 4.5.2, 4.6, 4.6.1 will reach End of Support on April 26, 2022[^]

If you want a solution for an earlier version of .NET, then you'll need to write a specific method for your data - eg:

public static DataTable DataToPivotTable(this IEnumerable<Data> source)
{
    var result = new DataTable();
    result.Columns.Add(new DataColumn("Section", typeof(string)));
    result.Columns.Add(new DataColumn("Lineitem", typeof(string)));
    result.Columns.Add(new DataColumn("BrokerCode", typeof(string)));
    result.Columns.Add(new DataColumn("BrokerName", typeof(string)));
    
    var sourceList = source.ToList();
    var periods = sourceList.Select(d => d.Period).Distinct(StringComparer.OrdinalIgnoreCase).ToList();
    foreach (string period in periods)
    {
        result.Columns.Add(new DataColumn(period, typeof(string)));
    }
    
    var groupedData = source.GroupBy(d => new
    {
        d.Section,
        d.Lineitem,
        d.BrokerCode,
        d.BrokerName
    });
    
    foreach (var group in groupedData)
    {
        var row = result.NewRow();
        row["Section"] = group.Key.Section;
        row["Lineitem"] = group.Key.Lineitem;
        row["BrokerCode"] = group.Key.BrokerCode;
        row["BrokerName"] = group.Key.BrokerName;
        
        foreach (var item in group)
        {
            row[item.Period] = item.PeriodValue;
        }
        
        result.Rows.Add(row);
    }
    
    return result;
}

DataTable pivotedData = _data.DataToPivotTable();

Output is identical to the previous method, but you won't be able to reuse this method for other input types or other sets of columns.

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

Re: C# How to convert my List<T> to Pivot datatable

Mou_kol14-Sep-21 23:54

Mou_kol

14-Sep-21 23:54

Sir, After reading your post i assume the code your provided can not be changed in such a way which can run in .net version 4.5.2

Thanks

Re: C# How to convert my List<T> to Pivot datatable

Richard Deeming15-Sep-21 0:38

Richard Deeming

15-Sep-21 0:38

The code in my first answer can't easily be changed to run in 4.5.2; you would need to provide your own class to represent the dynamic grouping.

The code in my reply should run in 4.5.2, but will only work for your specific Data class with the specific grouping from your question.

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

Re: C# How to convert my List<T> to Pivot datatable

Mou_kol15-Sep-21 3:49

Mou_kol

15-Sep-21 3:49

Sir the code you provided here is good but it is not flexible and as a result this can not be used in any scenario of pivot data re-presentation. your provided code is fixed and not dynamic.

can you please provide a extension method which convert any list to Pivot where i can supply those columns name on which grouping will be performed.

if possible please share anything similar code which really help me sir.

Thank you.

Re: C# How to convert my List<T> to Pivot datatable

Richard Deeming15-Sep-21 4:13

Richard Deeming

15-Sep-21 4:13

I have already provided you with such a code, but you rejected it because you've limited yourself to working in an obsolete version of the framework. A fact which you didn't mention until after I had given you the answer! D'Oh! | :doh:

I've also given you a fixed version for your stated requirement that works in your chosen obsolete framework version.

And now you want me to spend even more time rewriting the dynamic version of the code to work in your chosen obsolete framework version, when you appear to have put precisely zero effort into solving your own problem? WTF | :WTF:

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

Re: C# How to convert my List<T> to Pivot datatable

Mou_kol15-Sep-21 20:37

Mou_kol

15-Sep-21 20:37

My intension is not give any problem to anyone. i could not do that to customize the function body which can take multiple fields for grouping and that why i posted that question here and i thought some one will help.

anyway thanks for your help sir. cheers.

Re: C# How to convert my List<T> to Pivot datatable

Pete O'Hanlon15-Sep-21 6:52

Pete O'Hanlon

15-Sep-21 6:52

I assume you are going to pay Richard for the work. He has gone above and beyond to help you and you are demanding more and more. If you want the code writing for you, pay for it. Don't expect it for free.

Advanced TypeScript Programming Projects

Re: C# How to convert my List<T> to Pivot datatable

BillWoodruff15-Sep-21 8:52

BillWoodruff

15-Sep-21 8:52

You are very lucky to have any responses from a mind like Richard Deeming's !

Now, it's time for you to get to work.

«The mind is not a vessel to be filled but a fire to be kindled» Plutarch

Re: C# How to convert my List<T> to Pivot datatable

Mou_kol15-Sep-21 20:20

Mou_kol

15-Sep-21 20:20

How to store Pivot data into List<T>

Mou_kol14-Sep-21 5:53

Mou_kol

14-Sep-21 5:53

I am calling a store procedure and SP return data pivot way like below sample

+-----------------+--------------------+-------------+-------------------+----------+----------+-----------+----------+
|     Section     |      LineItem      | Broker Code |    Broker Name    | 2012 FYA | 2013 FYA | 1Q 2014A  | 2Q 2014A |
+-----------------+--------------------+-------------+-------------------+----------+----------+-----------+----------+
| Consensus Model | Net Revenue        | ZB          | B Securities      |          |          |    204.45 |   205.00 |
| Consensus Model | Net Revenue        | TU          | Cantor Fitzgerald |   101.33 |          |    204.45 |   201.00 |
| Consensus Model | Cost of Goods Sold | ZB          | B Securities      |          |          |    204.45 |   205.00 |
| Consensus Model | Cost of Goods Sold | TU          | Cantor Fitzgerald |   101.33 |          |    204.45 |   201.00 |
+-----------------+--------------------+-------------+-------------------+----------+----------+-----------+----------+

i easily can store the data into datatable but i am interested to know how could i store data into List<t> which return store procedure.

i do not want to use Datatable in the middle to hold SP return data.

this is my class where i like to store the above pivot data

public class Data
        {
            public string Section { get; set; }
            public string Lineitem { get; set; }
            public string BrokerCode { get; set; }
            public string BrokerName { get; set; }
            public string Period { get; set; }
            public string PeriodValue { get; set; }
        }

Thanks

Re: How to store Pivot data into List<T>

Richard Deeming14-Sep-21 6:04

Richard Deeming

14-Sep-21 6:04

Your class is designed to store the un-pivoted data. You'll need to change your query so that it doesn't pivot the data before you load it.

The alternative would be to redesign your class:

public class Header
{
    public string Section { get; set; }
    public string LineItem { get; set; }
    public string BrokerCode { get; set; }
    public string BrokerName { get; set; }
    public List<PeriodTotal> Periods { get; set; }
}

public class PeriodTotal
{
    public string Period { get; set; }
    public decimal PeriodValue { get; set; }
}

var result = new List<Header>();

using (var reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        string section = reader.GetString(0);
        string lineItem = reader.GetString(1);
        string brokerCode = reader.GetString(2);
        string brokerName = reader.GetString(3);
        
        var periods = new List<PeriodTotal>();
        for (int index = 4; index < reader.FieldCount; index++)
        {
            periods.Add(new PeriodTotal { Period = reader.GetName(index), PeriodValue = reader.GetDecimal(index) });
        }
        
        result.Add(new Header
        {
            Section = section,
            LineItem = lineItem,
            BrokerCode = brokerCode,
            BrokerName = brokerName,
            Periods = periods,
        });
    }
}

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

Re: How to store Pivot data into List<T>

Mou_kol14-Sep-21 6:29

Mou_kol

14-Sep-21 6:29

Sir, i can not change the SP which return data in pivot format. please guide me how to change class design which can hold pivot data?

suppose i used to store data into datatable from SP and datatable can store pivot data and later can't we use LINQ to extrac data from datatable and populate my class which i have posted here.

if possible can you please show me the way. thanks

Re: How to store Pivot data into List<T>

Richard Deeming14-Sep-21 6:49

Richard Deeming

14-Sep-21 6:49

Read my answer again. I've already shown you! D'Oh! | :doh:

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

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