C# split results of a query based on Date(Month) and Group

Question

Is there a way in C# of splitting results based on the date and a group?

Data:

Group | Create Date | Qty 
A     | 2012-03-01  | 5 
A     | 2012-02-01  | 1 
A     | 2012-02-01  | 3 
B     | 2012-02-01  | 4 
A     | 2012-01-01  | 1

Data Grid Would Display:

         Total | 2012/01 | 2012/02 | 2012|03
Group A: 10    | 1       | 4       | 5
GROUP B: 4     | 0       | 4       | 0

Is this something that can be achieved?

You'll need to work on Inner JOins to achieve that but yes it is possible. [Joins SQL](http://www.w3schools.com/sql/sql_join_inner.asp) as reading material. — Pedro Ferreira, Apr 03 '12 at 16:36
looks like you want a pivot, why don't you do that on the server side instead of the application side? — Taryn, Apr 03 '12 at 16:37
No time to write a full answer here, but if you are using SQL Server, the solution is through Dynamic SQL Pivot. Here is a great example: http://stackoverflow.com/a/7182489/570191 — Adriano Carneiro, Apr 03 '12 at 16:39

score 1 · Answer 1 · answered Apr 03 '12 at 16:52

Use group by and the loop over the results creating pivot. PS: Use LinqPad to see output.

 void Main()
{
    var data = new List<Entry> {
        new Entry { Group = "A", Date = DateTime.Parse("2012-03-01"), Qty = 5 },
        new Entry { Group = "A", Date = DateTime.Parse("2012-02-01"), Qty = 1 },
        new Entry { Group = "A", Date = DateTime.Parse("2012-02-01"), Qty = 3 },
        new Entry { Group = "B", Date = DateTime.Parse("2012-02-01"), Qty = 4 },
        new Entry { Group = "A", Date = DateTime.Parse("2012-01-01"), Qty = 1 }
    };
    data.GroupBy(d => new { d.Group, d.Date }).Dump();
}

class Entry
{
    public string Group { get; set; }
    public DateTime Date { get; set; }
    public int Qty { get; set; }
}

// Define other methods and classes here
class Button
{
    public Color BackColor
    { get; set;}
}

score 1 · Answer 2 · answered Apr 03 '12 at 18:38

The following LINQ query will return a result close to what you want.

You should be able to create the table from the result quite easily.

var data = new [] {
    new { Group = "A", Date = DateTime.Parse("2012-03-01"), Qty = 5 },
    new { Group = "A", Date = DateTime.Parse("2012-02-01"), Qty = 1 },
    new { Group = "A", Date = DateTime.Parse("2012-02-01"), Qty = 3 },
    new { Group = "B", Date = DateTime.Parse("2012-02-01"), Qty = 4 },
    new { Group = "A", Date = DateTime.Parse("2012-01-01"), Qty = 1 }
};

var result = from item in (
                from d in data
                group d by d.Group into EntryGroup
                select new {
                    EntryGroup.Key,
                    YearAndMonthGroups = 
                        from e in EntryGroup
                        let yearAndMonth = e.Date.ToString("yyyy-MM") 
                        group e by yearAndMonth into monthGroup
                        orderby monthGroup.Key
                        select new {
                            YearAndMonth = monthGroup.Key,
                            SubTotal = monthGroup.Sum(w => w.Qty)
                        }               
                })
            select new {
                Group = item.Key,
                Total = item.YearAndMonthGroups.Sum(s => s.SubTotal),
                YearAndMonthGroups = item.YearAndMonthGroups
            };

The result from LINQPad are
enter image description here

score 0 · Answer 3 · answered Apr 03 '12 at 17:05

you can do this using a PIVOT either with static/dynamic pivot. Below is an example of a static pivot:

create table #temp
(
    [group] varchar(1),
    createdate smalldatetime,
    qty int
)

insert into #temp values ('A', '3/1/2012', 5)
insert into #temp values ('A', '2/1/2012', 1)
insert into #temp values ('A', '2/1/2012', 3)
insert into #temp values ('B', '2/1/2012', 4)
insert into #temp values ('A', '1/1/2012', 1)

select [group]
    , total
    , isnull([1/1/2012], 0) as '2012/1'
    , isnull([2/1/2012], 0) as '2012/2'
    , isnull([3/1/2012], 0) as '2012/3'
from
(

    select t1.[group], t1.createdate, t1.qty, t2.total
    from #temp t1
    left join 
    (
        select [group], sum(qty) as Total
        from #temp 
        group by [group]
    ) t2
        on t1.[group] = t2.[group]
) x
pivot
(
    sum(qty)
    for createdate in ([3/1/2012], [2/1/2012], [1/1/2012])
) p
order by [group]

drop table #temp

for a dynamic pivot there are a lot of resources:

Pivots with Dynamic Columns in SQL Server 2005

Using SQL Server 2005/2008 Pivot on Unknown Number of Columns (Dynamic Pivot)

searching SO will give you a lot of answers on dynamic pivots as well.

C# split results of a query based on Date(Month) and Group

3 Answers3