Group By either column using C# LINQ

Question

I have a set of Data with columns such as below

OffName,RO1,RO2,RO3

To explain further i use sample data as below:

OffName RO1    RO2   RO3 
A       John   Jack  Rob 
B       Earl   John  Carl 
C       Rob    Chris Kenny 
D       Rodney Carl  Jacob 

RO stands for Reporting Officer. Each Officer reports to upto 3 RO's.i need to make a report where i need to show a grouping by RO irrespective of the person is RO1 or RO2 or RO3 for the officer..John is RO1 for Officer A and RO2 for Officer B, so when grouped by RO under John i want both Officer A & B to be picked.Same for Carl is RO3 for Officer B and RO2 for Officer D so when grouped by Ro under Carl both Officer B & D to be picked..

So for the above data when grouped by RO's i want the result to be as

RO    OffName 
John     A 
         B 
Jack     A 
Rob      A 
         C 
Earl     B 
Carl     B 
         D 
Chris    C 
Kenny    C 
Rodney   D 
Jacob    D 

Any help would b great

Thanks.

Answer 1

The easiest way is probably to "flatten" the problem and then do the group by:

    var query = officers.SelectMany(
        x => new[] {
            new { x.Name, RO = x.ReportingOfficer1 },
            new { x.Name, RO = x.ReportingOfficer2 },
            new { x.Name, RO = x.ReportingOfficer3 }
        }
    );
    var grouped = query.GroupBy(y => y.RO);
    foreach (var group in grouped) {
        foreach (var item in group) {
            Console.WriteLine(String.Format("{0}: {1}", item.RO, item.Name));
        }
    }

Here, I am assuming officers is a IEnumerable<Officer> where

class Officer {
    public string Name { get; set; }
    public string ReportingOfficer1 { get; set; }
    public string ReportingOfficer2 { get; set; }
    public string ReportingOfficer3 { get; set; }
}

With your sample data this is my output:

John: A
John: B
Jack: A
Rob: A
Rob: C
Earl: B
Carl: B
Carl: D
Chris: C
Kenny: C
Rodney: D
Jacob: D