Convert row values into columns using LINQ in c#

Question

I have a list as below

PillarId  Quarter  Feature
1         Q12106   France
1         Q12016   Germany 
1         Q22016   Italy
1         Q32016   Russia
2         Q22016   India
2         Q32016   USA
3         Q22016   China
3         Q32016   Australia
3         Q32016   New Zeland
3         Q42016   Japan

I want convert this into a list which looks like this

pillarId  Q12016 Q22016 Q32016       Q42016
1         France Italy  Russia
1         Germany 
2                India   USA
3                China   Australia    Japan
3                        New Zeland

Can anybody suggest some sample code

Thanks

Answer 1

You can use dynamic keyword to create object as per your requirement at run time. I am not inserting multiple entries for a pillerId. In my case i am adding multiple features in quarter field for a given pillerId. Please look into below code for the same:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Dynamic;

namespace Test
{
    public class Piller
    {
        public int PillarId;
        public string Quarter;
        public string Feature;

    public static List<dynamic> Generate()
    {
        List<Piller> pillers = new List<Piller>();
        pillers.Add(new Piller { PillarId = 1, Quarter = "Q12106", Feature = "France" });
        pillers.Add(new Piller { PillarId = 1, Quarter = "Q12106", Feature = "Germany" });
        pillers.Add(new Piller { PillarId = 1, Quarter = "Q22016", Feature = "Italy" });
        pillers.Add(new Piller { PillarId = 1, Quarter = "Q32016", Feature = "Russia" });
        pillers.Add(new Piller { PillarId = 2, Quarter = "Q22016", Feature = "Italy" });
        pillers.Add(new Piller { PillarId = 2, Quarter = "Q32016", Feature = "USA" });
        pillers.Add(new Piller { PillarId = 3, Quarter = "Q22016", Feature = "China" });
        pillers.Add(new Piller { PillarId = 3, Quarter = "Q32016", Feature = "Australia" });
        pillers.Add(new Piller { PillarId = 3, Quarter = "Q32016", Feature = "New Zeland" });
        pillers.Add(new Piller { PillarId = 3, Quarter = "Q42016", Feature = "Japan" });


        var pillerIds = (from p in pillers
                        select p.PillarId).Distinct();
        var quarters = (from p in pillers
                        select p.Quarter).Distinct();
        List<dynamic> transformedData = new List<dynamic>();

        foreach (var pillerId in pillerIds)
        {
            var data = new ExpandoObject() as IDictionary<string, Object>;
            data.Add("pillerId",pillerId);

            foreach (var quarter in quarters)
            {
                var features = (from p in pillers
                                where p.PillarId == pillerId && p.Quarter == quarter
                                select p.Feature);
                data.Add(quarter,features);
            }
            transformedData.Add(data);
        }
        return transformedData;

    }

    public static void Print(List<dynamic> data)
    {
        var dic = data[0] as IDictionary<string, Object>;
        foreach (var field in dic.Keys)
        {
            Console.Write(field+" ");
        }
        Console.WriteLine();
        foreach (dynamic record in data)
        {
            dic = record as IDictionary<string, Object>;
            foreach (var field in dic.Keys)
            {
                if (field == "pillerId")
                    Console.Write(dic[field] + " ");
                else
                {
                    var value = dic[field];
                    if (value == null)
                    {
                        Console.Write("      ");
                    }
                    else
                    {
                        StringBuilder sb = new StringBuilder();
                        foreach (var item in (value as IEnumerable<string>))
                        {
                            if (sb.Length == 0)
                                sb.Append(item);
                            else
                                sb.Append(","+item);
                        }
                        Console.Write(sb.ToString());
                    }
                }
                Console.Write(" ");
            }
            Console.WriteLine();

        }
    }

}

}

Answer 2

Try this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;


namespace ConsoleApplication16
{
    class Program
    {
         static void Main(string[] args)
        {
             DataTable dt = new DataTable();
             dt.Columns.Add("PillarId", typeof(int));
             dt.Columns.Add("Quarter", typeof(string));
             dt.Columns.Add("Feature", typeof(string));

             dt.Rows.Add(new object[] {1, "Q12116", "France"});
             dt.Rows.Add(new object[] {1, "Q12116", "Germany"});
             dt.Rows.Add(new object[] {1, "Q22116", "Italy"});
             dt.Rows.Add(new object[] {1, "Q32116", "Russia"});
             dt.Rows.Add(new object[] {2, "Q22116", "India"});
             dt.Rows.Add(new object[] {2, "Q32116", "USA"});
             dt.Rows.Add(new object[] {3, "Q22116", "China"});
             dt.Rows.Add(new object[] {3, "Q32116", "Austrailia"});
             dt.Rows.Add(new object[] {3, "Q32116", "New Zeland"});
             dt.Rows.Add(new object[] {3, "Q42116", "Japan"});

             string[] uniqueQuarters = dt.AsEnumerable().Select(x => x.Field<string>("Quarter")).Distinct().ToArray();

             var groups = dt.AsEnumerable()
                 .GroupBy(x => x.Field<int>("PillarId")).Select(x => x.GroupBy(y => y.Field<string>("Quarter")).Select(z => new { id = x.Key, quarter = z.Key, feature = z.Select((a,i) => new { feature = a.Field<string>("Feature"), index = i}).ToList()}).ToList()).ToList();

             DataTable pivot = new DataTable();
             pivot.Columns.Add("PillarId", typeof(int));
             foreach (string quarter in uniqueQuarters)
             {
                 pivot.Columns.Add(quarter, typeof(string));
             }

             foreach (var group in groups)
             {
                 int maxNewRows = group.Select(x => x.feature.Count()).Max();
                 for (int index = 0; index < maxNewRows; index++)
                 {
                     DataRow newRow = pivot.Rows.Add();
                     foreach (var row in group)
                     {
                         newRow["PillarId"] = row.id;
                         newRow[row.quarter] = row.feature.Skip(index) == null || !row.feature.Skip(index).Any() ? "" : row.feature.Skip(index).First().feature;
                     }
                 }
             }
        }
    }

}

Answer 3

This should work.

public struct temp
        {
            public int PillarID;
            public string Quarter;
            public string Feature;
        }
        static void Main(string[] args)
        {

        List<temp> list = new List<temp>
        {
            new temp {PillarID = 1, Quarter= "Q12106", Feature = "France"},
            new temp {PillarID = 1, Quarter= "Q12106", Feature = "Germany"},
            new temp {PillarID = 1, Quarter= "Q22016", Feature = "Italy"},
            new temp {PillarID = 1, Quarter= "Q32016", Feature = "Russia"},
            new temp {PillarID = 2, Quarter= "Q22016", Feature = "India"},
            new temp {PillarID = 2, Quarter= "Q32016", Feature = "USA"},
            new temp {PillarID = 3, Quarter= "Q22016", Feature = "China"},
            new temp {PillarID = 3, Quarter= "Q32016", Feature = "Australia"},
            new temp {PillarID = 3, Quarter= "Q32016", Feature = "New Zeland"},
            new temp {PillarID = 3, Quarter= "Q42016", Feature = "Japan"}

        };
            IEnumerable<IGrouping<string, temp>> byQuarter = list.GroupBy(x => x.Quarter);
            Console.WriteLine(byQuarter.ToString());
        }

It does order them in the wanted way, but displaying it isn't so simple. I'm currently trying to make it display nicely