Malisa Ncube - .NET Delights

.NET Development ideas and things
posts - 41 , comments - 86 , trackbacks - 0

My Links

News



I LOVE DataObject.NET
http://xceed.com
http://www.sharpcrafters.com/
http://www.telerik.com

Get this blog as a slideshow!
Powered by feedmap.net

Twitter












Tag Cloud

Archives

Post Categories

Simple Linq PIVOT to one row

My friend in Zimbabwe sent me an email a day ago with the following contents

suppose i have a table called week_days
with only 3 fields i.e.
SEQUENCE  DAY   SALES as follows:

SEQUENCE  DAY   SALES
1         Sun    23 
2         Mon    18
3         Tue    30
4         Wed    15  
5         Thu    20
6         Fri    08
7         Sat    0 

i need a query that converts DAY column
to a header row and sort by SEQUENCE as follows:

DAY    Sun Mon Tue Wed Thu Fri Sat
SALES  23  18  30  15  20  08  0 

Pliz Help!!!!!!!!
 
 
I was like’ this looks like a candidate for pivot, but it requires that the result be put in exactly on row. I cheated and sent him back this code. He was happy.
 
 
 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
 
namespace ConsoleApplication5
{
    class Program
    {
        static void Main(string[] args)
        {
                var week_days = new List<Sales>();
                week_days.Add(new Sales {Sequence = 1, Day = "Sun", Amount = 23});
                week_days.Add(    new Sales {Sequence = 2, Day = "Mon", Amount = 18});
                week_days.Add(    new Sales {Sequence = 3, Day = "Tue", Amount = 30});
                week_days.Add(    new Sales {Sequence = 4, Day = "Wed", Amount = 15}); 
                week_days.Add(    new Sales {Sequence = 5, Day = "Thu", Amount = 20});
                week_days.Add(    new Sales {Sequence = 6, Day = "Fri", Amount = 08});
                week_days.Add(    new Sales {Sequence = 7, Day = "Sat", Amount = 0});
 
                //Before transpose
                foreach(var day in week_days)
                  Console.WriteLine("{0}  {1}  {2}", day.Sequence, day.Day, day.Amount);
    
    
                var tr = from row in  week_days
                            group row by "SALES" into g
                            where g.FirstOrDefault() != null
                            select new
                    {
                            DAY = g.Key,
                            Sun = g.Where(sales => sales.Day == "Sun").Sum(sales => sales.Amount),
                            Mon = g.Where(sales => sales.Day == "Mon").Sum(sales => sales.Amount),
                            Tue = g.Where(sales => sales.Day == "Tue").Sum(sales => sales.Amount),
                            Wed = g.Where(sales => sales.Day == "Wed").Sum(sales => sales.Amount),
                            Thu = g.Where(sales => sales.Day == "Thu").Sum(sales => sales.Amount),
                            Fri = g.Where(sales => sales.Day == "Fri").Sum(sales => sales.Amount),
                            Sat = g.Where(sales => sales.Day == "Sat").Sum(sales => sales.Amount)
                    };
 
 
 
            foreach(var day in tr)
                  Console.Write("{0} {1} {2} {3} {4} {5} {6}", day.Sun, day.Mon, day.Tue, day.Wed, day.Thu, day.Fri, day.Sat);
 
            Console.ReadLine();
        }
    }
 
 
    public class Sales
    {
        public int Sequence {get; set;}
        public string Day { get; set;}
        public double Amount {get; set;}
    }
}
 
 
I should mention that I tried this code on my favourite ORM DataObjects.NET and I was not disappointed. Winking smile
 
Technorati Tags: ,,

Print | posted on Monday, December 5, 2011 4:35 AM |

Feedback

No comments posted yet.
Post A Comment
Title:
Name:
Email:
Comment:
Verification:
 

Powered by: