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?
https://stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq/6282689#6282689

Pivot data using LINQ
https://stackoverflow.com/questions/963491/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

I have done the job this way. here is two sample code.

1st Set of code

---

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


        }

    }
}

2nd Set of code

---

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

Answer 2

You already have it done in a T-SQL sproc. So why can't you use straight-up ADO.NET, SQL Command objects, run the sproc, use a datareader to read the result from the sproc populating a custom class/container object like a DTO, load the DTO into a List<T> and return the List<T>?

https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

Linq is not a remedy for everything.

Answer 3

Assuming the current data is stored in a datatable, please try the following code:

        static void Main(string[] args)  
        {  
            DataTable dataTable = new DataTable();  
            dataTable.Columns.Add("id", typeof(int));  
            dataTable.Columns.Add("name", typeof(string));  
            dataTable.Columns.Add("age", typeof(int));  
            dataTable.Rows.Add(1,"Tom",24);  
            dataTable.Rows.Add(2,"Tom1",18);  
            dataTable.Rows.Add(3,"Tom2",22);  
            dataTable.Rows.Add(4,"Tom3",33);  
            dataTable.Rows.Add(5,"Tom4",44);  
  
            DataTable dataTable1 = GetInversedDataTable(dataTable, "name", new string[] { "id"});  
            Console.WriteLine();  
  
        }  
        public static DataTable GetInversedDataTable(DataTable table, string columnX,  
                                             params string[] columnsToIgnore)  
        {  
            DataTable returnTable = new DataTable();  
  
            if (columnX == "")  
                columnX = table.Columns[0].ColumnName;  
  
            returnTable.Columns.Add(columnX);  
  
            List<string> columnXValues = new List<string>();  
            List<string> listColumnsToIgnore = new List<string>();  
            if (columnsToIgnore.Length > 0)  
                listColumnsToIgnore.AddRange(columnsToIgnore);  
  
            if (!listColumnsToIgnore.Contains(columnX))  
                listColumnsToIgnore.Add(columnX);  
  
            foreach (DataRow dr in table.Rows)  
            {  
                string columnXTemp = dr[columnX].ToString();  
                if (!columnXValues.Contains(columnXTemp))  
                {  
                    columnXValues.Add(columnXTemp);  
                    returnTable.Columns.Add(columnXTemp);  
                }  
                else  
                {  
                    throw new Exception("The inversion used must have " +  
                                        "unique values for column " + columnX);  
                }  
            }  
            foreach (DataColumn dc in table.Columns)  
            {  
                if (!columnXValues.Contains(dc.ColumnName) &&  
                    !listColumnsToIgnore.Contains(dc.ColumnName))  
                {  
                    DataRow dr = returnTable.NewRow();  
                    dr[0] = dc.ColumnName;  
                    returnTable.Rows.Add(dr);  
                }  
            }  
            for (int i = 0; i < returnTable.Rows.Count; i++)  
            {  
                for (int j = 1; j < returnTable.Columns.Count; j++)  
                {  
                    returnTable.Rows[i][j] =  
                      table.Rows[j - 1][returnTable.Rows[i][0].ToString()].ToString();  
                }  
            }  
  
            return returnTable;  
        }  

Where is your data stored, Excel file or another database?

Whichever it is, it should have a pivot function. I think it might be more convenient to call these functions in the code, and it can reduce the possibility of errors.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.