Group In both columns using C # LINQ

Question

I have a dataset with columns like below

OffName,RO1,RO2,RO3 

To explain further, I use the example data as shown 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 employee reports up to 3 RO's.i I need to make a report in which I need to show the RO grouping, regardless of whether the person is RO1 or RO2 or RO3 for the officer. John is RO1 for officers A and RO2 for officer B, so when the ROs are grouped under John, I want both officers A and B. to be selected. For Carl, this is RO3 for officers B and RO2 for officer D, so when Ro is grouped under Karl, both officers B and D will be selected ..

So, for the above data when grouping by RO, I want the result to be like

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

Any help would be great

Thanks.

Answer 1

The easiest way is to possibly “smooth out” the problem and then make the group:

  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 assume that officers are 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 conclusion:

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