none
Unpivot in Linq RRS feed

  • Question

  • How can I unpivot data using a LINQ query? I know there is a UNPIVOT operator in T-SQL but not sure what is the equivalent in LINQ.

    I am using .NET 3.5 Visual Studio 2008 SP1. I am using entity framework. I already have my model created.

    My table is as follows

    empid    empName    Jan    Feb

    1           John           100   110

    2           Mike            200    230

     

    I want the query to return:

    empid     empName     month     Sales

    1            John            Jan          100

    1            John            Feb           110

    2            Mike             Jan          200

    2            Mike             Feb          230

     

     

    Please let me know if you have any ideas or need more info from me. Thanks a lot!

    Saturday, October 16, 2010 12:01 AM

Answers

  • There is no equivalent UNPIVOT in linq, you need to do the convert yourself and customize the result step by step, something like,

               var result1 = (from p in context.Pivot_Table
                             select new temp
                             {
                                 empid = p.empid,
                                 empName = p.empName,
                                 Sales = p.Jan,
                                 month = "Jan"
                             }).ToList() ;

                var result2 = (from p in context.Pivot_Table
                             select new temp
                             {
                                 empid = p.empid,
                                 empName = p.empName,
                                 Sales = p.Feb,
                                 month = "Feb"
                             }).ToList() ;
               

                foreach(var item in result2)
                {
                    result1.Add(item);
                }
                
                dataGridView1.DataSource = result1.OrderBy(u => u.empid) .ToList();

    • Marked as answer by varosh Friday, October 29, 2010 9:57 PM
    Wednesday, October 20, 2010 5:59 AM

All replies

  • There is no equivalent UNPIVOT in linq, you need to do the convert yourself and customize the result step by step, something like,

               var result1 = (from p in context.Pivot_Table
                             select new temp
                             {
                                 empid = p.empid,
                                 empName = p.empName,
                                 Sales = p.Jan,
                                 month = "Jan"
                             }).ToList() ;

                var result2 = (from p in context.Pivot_Table
                             select new temp
                             {
                                 empid = p.empid,
                                 empName = p.empName,
                                 Sales = p.Feb,
                                 month = "Feb"
                             }).ToList() ;
               

                foreach(var item in result2)
                {
                    result1.Add(item);
                }
                
                dataGridView1.DataSource = result1.OrderBy(u => u.empid) .ToList();

    • Marked as answer by varosh Friday, October 29, 2010 9:57 PM
    Wednesday, October 20, 2010 5:59 AM
  • Thanks a lot for your help!
    Friday, October 29, 2010 9:57 PM