CODEWITHSUNDEEP
c#linq
Janty
Janty

Reputation: 1714

C# list data to pivot list

I have list of data in below format. I need to do pivot as ouput, but not getting done.

Input:

Vehicle         Service                 Value
GJ18-AU-8953    DIESEL FILTER SET       20555
GJ18-AU-8953    CROWN OIL 85W140 L/L    45445
GJ18-AU-8953    KAMANI BUSH             211212
GJ18-AU-8953    WHEEL SERVICE           4522
GJ18-AU-8953    ENGINE OIL 15W40 CI4+   42121
GJ18-AU-8953    STEERING OIL            454584
GJ18-AU-8953    AIR FILTER SET          454545
GJ18-AU-8953    TAPID WATER             212

Output:

Vehicle         DIESEL FILTER SET       CROWN OIL 85W140 L/L     KAMANI BUSH    WHEEL SERVICE   ENGINE OIL 15W40 CI4+   STEERING OIL    AIR FILTER SET  TAPID WATER 
GJ18-AU-8953    20555                   45445                    211212         4522            42121                    454584         454545           212

I referred through This, but unable to find the solution as we have column name generated at run-time.

Thank in advance.

Upvotes: 0

Views: 2463

Answers (1)

Martin Liversage
Martin Liversage

Reputation: 106826

By using the LINQ GroupBy operator you are able to both determine the columns in the output data as well as how to create the rows:

var columns = data.GroupBy(d => d.Service, (key, items) => key).ToList();
var groupedData = data.GroupBy(
  d => d.Vehicle,
  (key, items) => new {
    Key = key,
    Values = items.ToDictionary(item => item.Service, item => item.Value)
  }
);

This will only work if there is at most one service value for each vehicle. If you can have multiple values for the same combination of vehicle and service you need to specify how to aggregate these values and modify the ToDictionary accordingly.

You have not specified how you want to represent the output so here is a way to print the output so it matches the format you have used in your question (sort of - columns are separate by tab characters):

Console.WriteLine("Vehicle\t" + String.Join("\t", columns));
foreach (var group in groupedData) {
  Console.Write(group.Key);
  foreach (var column in columns)
    Console.Write("\t" + (group.Values.ContainsKey(column)
      ? group.Values[column].ToString() : String.Empty));
  Console.WriteLine();
}

Or you can create a DataTable from the data:

var dataTable = new DataTable();
dataTable.Columns.Add("Vehicle", typeof(String));
foreach (var column in columns)
  dataTable.Columns.Add(column, typeof(Int32));
foreach (var group in groupedData) {
  var values = new[] { group.Key }
    .Concat(
      columns.Select(
        column => group.Values.ContainsKey(column)
          ? (Object) group.Values[column] : DBNull.Value
      )
    )
    .ToArray();
  dataTable.Rows.Add(values);
}

Upvotes: 3

Related Questions