none
Pivot: How can i store Pivot data into List<T> RRS feed

  • Question

  • I am calling a sql server store procedure which return pivot data which i store in data table but i like to know how could i design my call as a result i can store Pivot format data in my List<T> 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<T> class instead of data table.

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

    thanks

    Sunday, July 5, 2020 6:06 PM

Answers

  • Hi Sudip_inn,
    I found jdweng has provided a code example in stack overflow.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Wednesday, July 8, 2020 6:27 AM
    Wednesday, July 8, 2020 6:04 AM

All replies

  • I don't have appropriate environment to test and code it, but I propose the following:

    You can store the returned data in SortedDictionary<string, List<decimal>> and then the "financial quarter names" in List<string>.

    Then loop the fields of first row with SqlDataReader.GetName() to get the "financial quarter names" as column header, and SqlDataReader.GetDecimal() to read them into List<decimal> and add it to SortedDictionary<string, List<decimal>> with SqlDataReader.GetString(0) as key when read all data in the row.

    ======

    Alternatively you may stored both axis in corresponding List<string> variable, then store the value as SortedDictionary<Tuple<string, string>, decimal> with SqlDataReader.GetDecimal().

    ======

    Or you can just use SqlDataAdapter to fill the result into DataTable. IMO DataTable is the most effective way to handle Pivot data with unknown/unlimited columns.

    If you create a data type that inherits DataTable to store the data, you may add extension method that use LINQ to search on dataTable.AsEnumerable() to return DataRow based on Description as well.




    Monday, July 6, 2020 2:56 AM
    Answerer
  • Hi Sudip_inn,
    You can try to pivot data in List<T> with Linq.
    And here is a code example you can refer to.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 6, 2020 2:59 AM
  • please come with a small sample code where data table will not be used to pivot data rather List<T> will store the pivot data. thanks
    Tuesday, July 7, 2020 1:09 PM
  • Hi Daniel,

    The thread you pointed out is helpful, but the question here is how to store the returned result properly, not how to actually do the PIVOT of the data.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 7, 2020 1:36 PM
    Moderator
  • Hi Sudip_inn,
    I found jdweng has provided a code example in stack overflow.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Wednesday, July 8, 2020 6:27 AM
    Wednesday, July 8, 2020 6:04 AM
  • This code is from @jdweng

    --------------------- 1st Set of 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; }
        }
    
    
    }
    
    
    --------------------- Second Set of 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);
    
                    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; }
        }
    
    
    }
    https://stackoverflow.com/a/62776564/13722367

    Friday, July 10, 2020 6:47 AM