Malisa Ncube - .NET Delights

.NET Development ideas and things
posts - 41 , comments - 84 , 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

Monday, December 5, 2011

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: ,,

Posted On Monday, December 5, 2011 4:35 AM | Comments (0) |

50% off on DataObjects.NET

I have been closely following the updates on http://x-tensive.com/ and been impressed with the new features that keep getting added into DataObjects.NET. A few days ago, I saw the 50% sale blog post here and thought it would be very interesting for anyone that wishes to start using DataObjects.NET but feared could not afford the price.

DataObjects.NET ORM is very feature rich, well designed and will certainly save you countless development hours by making you think about the domain and code rather than SQL. For your information the new fact aggregation and comparing site http://9facts.com uses DataObjects.NET behind the scenes. Smile For me, that is a testament that shows versatility of a product. The X-tensive guys decided on dogfooding their own product and it has worked very nicely.

Links

http://dataobjects.net

http://blog.dataobjects.net/2011/12/big-winter-sale.html

http://blog.dataobjects.net/2011/11/upcoming-major-update.html

http://9facts.com/About

http://blog.x-tensive.com/2011/11/weve-launched-9factscom.html

 

Technorati Tags: ,

Posted On Monday, December 5, 2011 12:41 AM | Comments (0) |

Powered by: