How to create new columns from List values?

Question

I've a list like this:

internal class Order
{
    public Order()
    {
    }

    public string ProductID { get; set; }
    public int Amount { get; set; }
    public string Date { get; set; }
}

List<Order> orderList = new List<Order>() 
{
    new Order(){ ProductID="12345", Amount=300, Date = "2018-12-19"},
    new Order(){ ProductID="12345", Amount=0, Date = "2018-12-20"},
    new Order(){ ProductID="12345", Amount=200, Date = "2018-12-21"},
    new Order(){ ProductID="12345", Amount=250, Date = "2018-12-22"},
    new Order(){ ProductID="12345", Amount=30, Date = "2018-12-23"},
    new Order(){ ProductID="67898", Amount=20, Date = "2018-12-20"},
    new Order(){ ProductID="67898", Amount=30, Date = "2018-12-21"},
    new Order(){ ProductID="67898", Amount=40, Date = "2018-12-22"},
    new Order(){ ProductID="67898", Amount=50, Date = "2018-12-23"},
    new Order(){ ProductID="67898", Amount=130, Date = "2018-12-24"}
};

In this situation, when I convert the list to DataTable and export it to excel, I get this:

I want to create new columns from Date values and list them by ProductID. How can I convert this List to DataTable to see like this:

Use `ToDataTable` https://stackoverflow.com/a/42550827/2946329 — Salah Akbari, Dec 19 '18 at 11:25
Use GroupBy(x => x.Date) and you have all Orders for every Date. And then you can add the rows like this: https://stackoverflow.com/questions/9258704/how-to-group-by-the-datatable-and-put-the-result-in-another-datatable — Presi, Dec 19 '18 at 11:47

score 1 · Answer 1 · answered Dec 19 '18 at 12:11

You can achieve this by using GroupBy and ExpandoObject:

var result = new List<ExpandoObject>();
foreach (var orders in orderList.GroupBy(obj => obj.ProductID))
{
    var record = new ExpandoObject();
    foreach (var order in orders.AsEnumerable())
    {
        ((IDictionary<string, object>)record).Add(order.Date, order.Amount);
    }
    result.Add(record);
}

score 0 · Accepted Answer · answered Dec 19 '18 at 12:15

You want a pivot table like in excel. The code below creates two datatables. First one base one you list. Then it take first table and create second pivot table

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication93
{
    class Program
    {
        static void Main(string[] args)
        {
            Order order = new Order();
            DataTable dt = order.MakeTable();

            //now create piviot table
            string[] dates = dt.AsEnumerable().Select(x => x.Field<string>("Date")).Distinct().ToArray();

            DataTable pivot = new DataTable();
            pivot.Columns.Add("ProductID", typeof(string));
            foreach (string date in dates)
            {
                pivot.Columns.Add(date, typeof(int));
            }

            var groups = dt.AsEnumerable().GroupBy(x => x.Field<string>("ProductID")).ToList();

            foreach(var group in groups)
            {
                DataRow newRow = pivot.Rows.Add();
                newRow["ProductID"] = group.Key;
                foreach (var col in group)
                {
                    newRow[col.Field<string>("Date")] = col.Field<int>("Amount");
                }

            }

        }
    }
    internal class Order
    {
        public Order()
        {
        }

        public string ProductID { get; set; }
        public int Amount { get; set; }
        public string Date { get; set; }

        public DataTable MakeTable()
        {
            DataTable dt = new DataTable();

            List<Order> orderList = new List<Order>() {
                new Order(){ ProductID="12345", Amount=300, Date = "2018-12-19"},
                new Order(){ ProductID="12345", Amount=0, Date = "2018-12-20"},
                new Order(){ ProductID="12345", Amount=200, Date = "2018-12-21"},
                new Order(){ ProductID="12345", Amount=250, Date = "2018-12-22"},
                new Order(){ ProductID="12345", Amount=30, Date = "2018-12-23"},
                new Order(){ ProductID="67898", Amount=20, Date = "2018-12-20"},
                new Order(){ ProductID="67898", Amount=30, Date = "2018-12-21"},
                new Order(){ ProductID="67898", Amount=40, Date = "2018-12-22"},
                new Order(){ ProductID="67898", Amount=50, Date = "2018-12-23"},
                new Order(){ ProductID="67898", Amount=130, Date = "2018-12-24"}
            };

            foreach (var prop in this.GetType().GetProperties())
            {
                string typeName = prop.PropertyType.FullName;
                Type systemType = System.Type.GetType(typeName);
                dt.Columns.Add(prop.Name, systemType);
            }
            foreach (Order row in orderList)
            {
                object[] data = row.GetType().GetProperties().Select(x => x.GetValue(row, null)).ToArray();
                dt.Rows.Add(data);
            }

            return dt;
        }
    }
}

How to create new columns from List values?

2 Answers2