LINQ to Swap few Columns to Rows of a DataTable us

问题:

I've datatable:

location    Quarter   ppl_required   ppl_available
BLR          Q1        70             35
BLR          Q2        50             45
BLR          Q3        25             28
BLR          Q4        60             58
CHN          Q1        77             92
CHN          Q2        42             66
CHN          Q3        29             20
CHN          Q4        22             24

Is there a better way to get the below DataTable as output in very simple or short way [without loops] using LINQ or with advanced features of LINQ either with .NET3.5/4.0/4.5 framework.

Location  ppl_Required_Q1  ppl_Required_Q2  ppl_Required_Q3  ppl_Required_Q4  ppl_available_Q1  ppl_available_Q2  ppl_available_Q3  ppl_available_Q4
BLR       70               50               25               60               35                45                28                58
CHN       77               42               29               22               92                66                20                24

回答1:

I'm not sure what you've tried, if there are any efficiency or flexibility needs, or what you really need for an output container, but perhaps something as simple as this is useful. Assuming dt is your datatable:

var newSet = dt.AsEnumerable()
               .GroupBy(r => r.Field<string>("Location"))
               .Select(g => new
               {
                    Location = g.Key,
                    ppl_required_Q1 = g.Where(p => p.Field<string>("Quarter") == "Q1").Sum(p => p.Field<int>("ppl_required")),
                    ppl_required_Q2 = g.Where(p => p.Field<string>("Quarter") == "Q2").Sum(p => p.Field<int>("ppl_required")),
                    ppl_required_Q3 = g.Where(p => p.Field<string>("Quarter") == "Q3").Sum(p => p.Field<int>("ppl_required")),
                    ppl_required_Q4 = g.Where(p => p.Field<string>("Quarter") == "Q4").Sum(p => p.Field<int>("ppl_required")),
                    ppl_available_Q1 = g.Where(p => p.Field<string>("Quarter") == "Q1").Sum(p => p.Field<int>("ppl_available")),
                    ppl_available_Q2 = g.Where(p => p.Field<string>("Quarter") == "Q2").Sum(p => p.Field<int>("ppl_available")),
                    ppl_available_Q3 = g.Where(p => p.Field<string>("Quarter") == "Q3").Sum(p => p.Field<int>("ppl_available")),
                    ppl_available_Q4 = g.Where(p => p.Field<string>("Quarter") == "Q4").Sum(p => p.Field<int>("ppl_available")),
                });

EDIT

Adding an extension method assembled from examples here and here in case links get broken in the future. You should be able to modify this as needed.

public static DataTable ToDataTable<T>(this IEnumerable<T> source, string newTableName)
{
    DataTable newTable = new DataTable(newTableName);

    T firstRow = source.FirstOrDefault();
    if (firstRow != null)
    {
        PropertyInfo[] properties = firstRow.GetType().GetProperties();
        foreach (PropertyInfo prop in properties)
        {
            newTable.Columns.Add(prop.Name, prop.PropertyType);
        }

        foreach (T element in source)
        {
            DataRow newRow = newTable.NewRow();
            foreach (PropertyInfo prop in properties)
            {
                newRow[prop.Name] = prop.GetValue(element, null);
            }
            newTable.Rows.Add(newRow);
        }
    }
    return newTable;
}

回答2:

I created a similar data structure to what you described in LINQPad this is the code that I have

void Main()
{
    List<Location> locations = new List<Location>
   {
      new Location { Key = "BLR", Quarter = "Q1", PeopleRequired = 70, PeopleAvailable = 35 },
      new Location { Key = "BLR", Quarter = "Q2", PeopleRequired = 50, PeopleAvailable = 45 },
      new Location { Key = "BLR", Quarter = "Q3", PeopleRequired = 25, PeopleAvailable = 28 },
      new Location { Key = "BLR", Quarter = "Q4", PeopleRequired = 60, PeopleAvailable = 58 },
      new Location { Key = "CHN", Quarter = "Q1", PeopleRequired = 77, PeopleAvailable = 92 },
      new Location { Key = "CHN", Quarter = "Q2", PeopleRequired = 42, PeopleAvailable = 66 },
      new Location { Key = "CHN", Quarter = "Q3", PeopleRequired = 29, PeopleAvailable = 20 },
      new Location { Key = "CHN", Quarter = "Q4", PeopleRequired = 22, PeopleAvailable = 24 },
      new Location { Key = "CAD", Quarter = "Q1", PeopleRequired = 100, PeopleAvailable = 150 },
      new Location { Key = "CAD", Quarter = "Q2", PeopleRequired = 200, PeopleAvailable = 250 },
   };

   var results =
   (
      from loc in locations.Select(l => new { l.Key }).Distinct()
      join q1 in locations.Where(l => l.Quarter == "Q1") on loc.Key equals q1.Key into quarter1
      join q2 in locations.Where(l => l.Quarter == "Q2") on loc.Key equals q2.Key into quarter2
      join q3 in locations.Where(l => l.Quarter == "Q3") on loc.Key equals q3.Key into quarter3
      join q4 in locations.Where(l => l.Quarter == "Q4") on loc.Key equals q4.Key into quarter4
      from q1 in quarter1.DefaultIfEmpty()
      from q2 in quarter2.DefaultIfEmpty()
      from q3 in quarter3.DefaultIfEmpty()
      from q4 in quarter4.DefaultIfEmpty()
      select new
      {
         loc.Key,
         Q1_PeopleRequired  = q1 != null ? q1.PeopleRequired  : -1,
         Q1_PeopleAvailable = q1 != null ? q1.PeopleAvailable : -1,
         Q2_PeopleRequired  = q2 != null ? q2.PeopleRequired  : -1,
         Q2_PeopleAvailable = q2 != null ? q2.PeopleAvailable : -1,
         Q3_PeopleRequired  = q3 != null ? q3.PeopleRequired  : -1,
         Q3_PeopleAvailable = q3 != null ? q3.PeopleAvailable : -1,
         Q4_PeopleRequired  = q4 != null ? q4.PeopleRequired  : -1,
         Q4_PeopleAvailable = q4 != null ? q4.PeopleAvailable : -1
      }
   );

   results.Dump();
}

// Define other methods and classes here
public class Location
{
   public string Key          { get; set; }
   public string Quarter      { get; set; }
   public int PeopleRequired  { get; set; }
   public int PeopleAvailable { get; set; }
}

The result that I ended up with is what you were looking for. This above may or may not be the BEST way and I make lag on a large table but it works :)

Key | Q1_PeopleRequired | Q1_PeopleAvailable | Q2_PeopleRequired | Q2_PeopleAvailable | Q3_PeopleRequired | Q3_PeopleAvailable | Q4_PeopleRequired | Q4_PeopleAvailable 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
BLR | 70                | 35                 | 50                | 45                 | 25                | 28                 | 60                | 58
CHN | 77                | 92                 | 42                | 66                 | 29                | 20                 | 22                | 24
CAD | 100               | 150                | 200               | 250                | -1                | -1                 | -1                | -1