C# How can i store Pivot data into List<T>

Question

I am calling a sql server store procedure which return pivot data now which i store in data table but i like to know how could i design my class as a result i can store Pivot format data in my List class.

here i am giving a screen shot which show what kind of data i am dealing with.

all the quarter data is not fixed. it is coming from database and it can be up-to any year. so please guide how to design my class as a result store the above data in my List class instead of data table.

if possible please explain this with a sample code and scenario.

thanks

Answer 1

Try following code :

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

namespace ConsoleApplication8
{
    class Program
    {
        static void Main(string[] args)
        {
            List<Revenue> revenues = new List<Revenue>();
            DataTable dt = new DataTable();
            dt.Columns.Add("Description", typeof(string));
            for (int quarter = 1; quarter <= 4; quarter++)
            {
                for (int year = 2019; year < 2020; year++)
                {
                    dt.Columns.Add(quarter.ToString() + "Q " + year.ToString() + "A",typeof(decimal));
                }
            }
            dt.Rows.Add(new object[] { "Mainline", 7082.5, 8504, 8552.7 });
            dt.Rows.Add(new object[] { "Regional Carriers", 1642.5, 1982, 1928,3 });
            dt.Rows.Add(new object[] { "Total Passenger Revenue", 8725, 10.486, 10.481, 9933 });

            for (int col = 1; col < dt.Rows.Count; col++)
            {
                Revenue revenue = new Revenue();
                revenue.quarter = dt.Columns[col].ColumnName;
                revenues.Add(revenue);
                foreach (DataRow row in dt.AsEnumerable())
                {

                    switch (row.Field<string>("Description"))
                    {
                        case "Mainline" :
                            revenue.Mainline = (decimal)row[col];
                            break;
                        case "Regional Carriers":
                            revenue.RegionalCarriers = (decimal)row[col];
                            break;
                        case "Total Passenger Revenue":
                            revenue.TotalPassengerRevenue = (decimal)row[col];
                            break;
                    }
                }
            }
 
        }

    }

    public class Revenue
    {
        public string quarter { get; set; }
        public decimal Mainline { get; set; }
        public decimal RegionalCarriers { get; set; }
        public decimal TotalPassengerRevenue { get; set; }
    }


}

---

Here is a more generic approach using a dictionary

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

namespace ConsoleApplication8
{
    class Program
    {
        static void Main(string[] args)
        {
            List<Revenue> revenues = new List<Revenue>();
            DataTable dt = new DataTable();
            dt.Columns.Add("Description", typeof(string));
            for (int quarter = 1; quarter <= 4; quarter++)
            {
                for (int year = 2019; year < 2020; year++)
                {
                    dt.Columns.Add(quarter.ToString() + "Q " + year.ToString() + "A",typeof(decimal));
                }
            }
            dt.Rows.Add(new object[] { "Mainline", 7082.5, 8504, 8552.7 });
            dt.Rows.Add(new object[] { "Regional Carriers", 1642.5, 1982, 1928,3 });
            dt.Rows.Add(new object[] { "Total Passenger Revenue", 8725, 10.486, 10.481, 9933 });

            for (int col = 1; col < dt.Rows.Count; col++)
            {
                Revenue revenue = new Revenue();
                revenue.quarter = dt.Columns[col].ColumnName;
                revenues.Add(revenue);

                revenue.dict = dt.AsEnumerable().Select(x => new { description = x.Field<string>("Description"), amount = (decimal)x[col] })
                    .GroupBy(x => x.description, y => y.amount)
                    .ToDictionary(x => x.Key, y => y.FirstOrDefault());
            }
 
        }

    }

    public class Revenue
    {
        public string quarter { get; set; }
        public Dictionary<string, decimal> dict { get; set; }
    }


}