How convert SQL Server Pivot functionality in C# uisng LINQ

Question

I am using SQL Server Pivot which works fine. Here is sample data below which transpose by SQL server pivot function. this is sample data which convert to pivot by SQL server.

+------------+--------------+-----------+--------------+--------+-------------+--------------------+----------+
| RowNumber  |   Section    | LineItem  | DisplayInCSM | Broker | BrokerName  | ItemValue_NoFormat |  Period  |
+------------+--------------+-----------+--------------+--------+-------------+--------------------+----------+
|          1 | Operational  | NG Sales  | NGL          | CR     | Credit Suse |                200 | 2010 FYA |
|          2 | Operational  | NG Sales  | NGL          | GR     | Max 1       |                300 | 2010 FYA |
|          3 | Operational  | NG Sales  | NGL          | PX     | Morgan      |                100 | 2010 FYA |
|          4 | Operational  | NG Sales  | NGL          | WB     | Wells Fargo |                500 | 2010 FYA |
+------------+--------------+-----------+--------------+--------+-------------+--------------------+----------+

This is dynamic sql i used in sql server to represent data in pivot format. here it is.

SET @SQL='SELECT *                                      
FROM                                                                              
(                      
  SELECT RowNumber,CAST(ISNULL(EarningID,0) AS INT) EarningID,    
  Section,    
  LineItem,    
  DisplayInCSM,     
  Type,     
  Broker,    
  BrokerName,     
  ItemValue_NoFormat,     
  TRIM(ISNULL(Period,'''')) Period,hierarchy,                
  from #tmpData1 WHERE TYPE<>''SHEET''                                                                      
) t                                                                              
PIVOT                                                                              
(                                                                              
 MAX(ItemValue_NoFormat)                                                                              
 FOR Broker IN ([5W], [8K], [CL], [DA], [EQ], [FA], [GS], [HM], [HQ], [JY], [KW], [ML], [MS], [MV], [SL], [UA],[WB])                                                                              
) AS P                                                                              
order by hierarchy,PeriodOrder   

Now due to some problem i have to use C# to pivot data which is stored in list. suppose my first sample stored in list now how can i pivot that data by c# LINQ.

i saw these post Is it possible to Pivot data using LINQ?

Pivot data using LINQ

but still not clear to me how to write the code for my scenario which display broker name horizontally. so please some one give me some hint which help me to start the coding part as a result i can show my data in pivot format where broker name will be shown horizontally. thanks

Answer 1

Try following :

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

namespace ConsoleApplication
{
    class Program
    {
         static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("RowNumber", typeof(int));
            dt.Columns.Add("Section", typeof(string));
            dt.Columns.Add("LineItem", typeof(string));
            dt.Columns.Add("DisplayInCSM", typeof(string));
            dt.Columns.Add("Broker", typeof(string));
            dt.Columns.Add("BrokerName", typeof(string));
            dt.Columns.Add("ItemValue_NoFormat", typeof(int));
            dt.Columns.Add("Period", typeof(string));

            dt.Rows.Add(new object[] {1, "Operational", "NG Sales", "NGL", "CR", "Credit Suse", 200, "2010 FYA"});
            dt.Rows.Add(new object[] {2, "Operational", "NG Sales", "NGL", "GR", "Max 1", 300, "2010 FYA"});
            dt.Rows.Add(new object[] {3, "Operational", "NG Sales", "NGL", "PX", "Morgan", 100,"2010 FYA"});
            dt.Rows.Add(new object[] {4, "Operational", "NG Sales", "NGL", "WB", "Wells Fargo", 500, "2010 FYA"});

            string[] brokers = dt.AsEnumerable().Select(x => x.Field<string>("Broker")).Distinct().OrderBy(x => x).ToArray();

            DataTable pivot = new DataTable();
            pivot.Columns.Add("Section", typeof(string));
            pivot.Columns.Add("LineItem", typeof(string));
            pivot.Columns.Add("DisplayInCSM", typeof(string));

            foreach (string broker in brokers)
            {
                pivot.Columns.Add(broker, typeof(int));
            }
            var groups = dt.AsEnumerable().GroupBy(x => new { section = x.Field<string>("Section"), lineItem = x.Field<string>("LineItem"), csm = x.Field<string>("DisplayInCSM")}).ToList();

            foreach (var group in groups)
            {
                DataRow newRow = pivot.Rows.Add();
                newRow["Section"] = group.Key.section;
                newRow["LineItem"] = group.Key.lineItem;
                newRow["DisplayInCSM"] = group.Key.csm;
                foreach (DataRow row in group)
                {
                    newRow[row.Field<string>("Broker")] = row.Field<int>("ItemValue_NoFormat");
                }
            }

            
        }

    }
}

---

Here is code using a List

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

namespace ConsoleApplication
{
    class Program
    {
         static void Main(string[] args)
        {
            List<CSM> csms = new List<CSM>() {
                new CSM(1, "Operational", "NG Sales", "NGL", "CR", "Credit Suse", 200, "2010 FYA"),
                new CSM(2, "Operational", "NG Sales", "NGL", "GR", "Max 1", 300, "2010 FYA"),
                new CSM(3, "Operational", "NG Sales", "NGL", "PX", "Morgan", 100,"2010 FYA"),
                new CSM(4, "Operational", "NG Sales", "NGL", "WB", "Wells Fargo", 500, "2010 FYA")
            };

            string[] brokers = csms.Select(x => x.Broker).Distinct().OrderBy(x => x).ToArray();

            DataTable pivot = new DataTable();
            pivot.Columns.Add("Section", typeof(string));
            pivot.Columns.Add("LineItem", typeof(string));
            pivot.Columns.Add("DisplayInCSM", typeof(string));

            foreach (string broker in brokers)
            {
                pivot.Columns.Add(broker, typeof(int));
            }
            var groups = csms.GroupBy(x => new { section = x.Section, lineItem = x.LineNumber, csm = x.DisplayInCSM}).ToList();

            foreach (var group in groups)
            {
                DataRow newRow = pivot.Rows.Add();
                newRow["Section"] = group.Key.section;
                newRow["LineItem"] = group.Key.lineItem;
                newRow["DisplayInCSM"] = group.Key.csm;
                foreach (CSM row in group)
                {
                    newRow[row.Broker] = row.ItemValue_NoFormat;
                }
            }

            
        }

    }
    public class CSM
    {
        public int RowNumber { get;set;}
        public string LineNumber { get; set; }
        public string Section { get; set; }
        public string DisplayInCSM { get;set;}
        public string Broker { get;set;}
        public string BrokerName { get;set;}
        public int ItemValue_NoFormat { get;set;}
        public string Period{ get;set;}

        public CSM() {}
        public CSM(
            int RowNumber,
            string LineNumber,
            string Section,
            string DisplayInCSM,
            string Broker,
            string BrokerName,
            int ItemValue_NoFormat,
            string Period)
            {
                this.RowNumber = RowNumber;
                this.LineNumber = LineNumber;
                this.Section = Section;
                this.DisplayInCSM = DisplayInCSM;
                this.Broker = Broker;
                this.BrokerName = BrokerName;
                this.ItemValue_NoFormat = ItemValue_NoFormat;
                this.Period = Period;
            }
     }
}