OpenStack OpenStack - 3 months ago 6
C# Question

Pivoting multiple columns in C#

I have a C# list of object which I need to pivot and transform. But I am not sure how to get the desired result. The code below talks about structure, the data and the desired result. I know, I need to first group the data and then pivot it. But I am confused how to achieve it.

class Program
{
static void Main(string[] args)
{
List<Rev> revList = new List<Rev>();
Rev r = new Rev { Id = 1, Name = "One", Rank = 1, Region = "Global", Revenue = 600 }; revList.Add(r);
r = new Rev { Id = 1, Name = "One", Rank = 1, Region = "USA", Revenue = 100 }; revList.Add(r);
r = new Rev { Id = 1, Name = "One", Rank = 1, Region = "Euro", Revenue = 200 }; revList.Add(r);
r = new Rev { Id = 1, Name = "One", Rank = 1, Region = "APAC", Revenue = 300 }; revList.Add(r);

r = new Rev { Id = 2, Name = "Two", Rank = 2, Region = "Global", Revenue = 500 }; revList.Add(r);
r = new Rev { Id = 2, Name = "Two", Rank = 2, Region = "USA", Revenue = 100 }; revList.Add(r);
r = new Rev { Id = 2, Name = "Two", Rank = 3, Region = "APAC", Revenue = 400 }; revList.Add(r);

r = new Rev { Id = 3, Name = "Three", Rank = 2, Region = "Global", Revenue = 300 }; revList.Add(r);
r = new Rev { Id = 3, Name = "Three", Rank = 2, Region = "USA", Revenue = 100 }; revList.Add(r);
r = new Rev { Id = 3, Name = "Three", Rank = 3, Region = "Euro", Revenue = 200 }; revList.Add(r);

//Should result in THIS IS THE HARD CODED RESULT WHICH TRANFORM FUNCTION SHOULD GENERATE
List<RevExtended> resultList = new List<RevExtended>();
RevExtended res = new RevExtended{Name = "One", Id = 1, APACRevenue = 300, ApacRank = 1, EuroRank = 1, EurpRevenue = 200, GlobalRank = 1, GlobalRevenue = 600, USARank = 1, USARevenue = 100};
res = new RevExtended { Name = "Two", Id = 2, APACRevenue = 400, ApacRank = 3, GlobalRank = 2, GlobalRevenue = 500, USARank = 2, USARevenue = 100 };
res = new RevExtended { Name = "Three", Id = 3, EuroRank = 3, EurpRevenue = 200, GlobalRank = 2, GlobalRevenue = 300, USARank = 2, USARevenue = 100 };
}

public List<RevExtended> Transform(List<Rev> revList)
{
List<RevExtended> resultList = new List<RevExtended>();

var query = from rev in revList
group rev by rev.Id into revGroup


return resultList;
}
}

public class Rev
{
public string Name { get; set; }
public int Id { get; set; }
public int Revenue { get; set; }
public int Rank { get; set; }
public string Region { get; set; }
}

public class RevExtended
{
public string Name { get; set; }
public int Id { get; set; }

public int GlobalRevenue { get; set; }
public int GlobalRank { get; set; }

public int USARevenue { get; set; }
public int USARank { get; set; }

public int EurpRevenue { get; set; }
public int EuroRank { get; set; }

public int APACRevenue { get; set; }
public int ApacRank { get; set; }
}

Answer

Here's one approach you could take:

var pivoted =
    from d in data
    group d by new { d.Id, d.Name } into region
    let g = region.SingleOrDefault(d => d.Region == "Global") ?? new Rev()
    let u = region.SingleOrDefault(d => d.Region == "USA") ?? new Rev()
    let e = region.SingleOrDefault(d => d.Region == "Euro") ?? new Rev()
    let a = region.SingleOrDefault(d => d.Region == "APAC") ?? new Rev()
    select new RevExtended
    {
        Id = region.Key.Id,
        Name = region.Key.Name,
        GlobalRevenue = g.Revenue,
        GlobalRank = g.Rank,
        UsaRevenue = u.Revenue,
        UsaRank = u.Rank,
        EuroRevenue = e.Revenue,
        EuroRank = e.Rank,
        ApacRevenue = a.Revenue,
        ApacRank = a.Rank,
    };

If you need null values in the absence of a region, you need to change the models to be able to support them.

public class RevExtended
{
    public int Id { get; set; }
    public string Name { get; set; }

    public int? GlobalRank { get; set; }
    public int? GlobalRevenue { get; set; }

    public int? UsaRank { get; set; }
    public int? UsaRevenue { get; set; }

    public int? EuroRank { get; set; }
    public int? EuroRevenue { get; set; }

    public int? ApacRank { get; set; }
    public int? ApacRevenue { get; set; }
}
var pivoted =
    from d in data
    group d by new { d.Id, d.Name } into region
    let g = region.SingleOrDefault(d => d.Region == "Global")
    let u = region.SingleOrDefault(d => d.Region == "USA")
    let e = region.SingleOrDefault(d => d.Region == "Euro")
    let a = region.SingleOrDefault(d => d.Region == "APAC")
    select new RevExtended
    {
        Id = region.Key.Id,
        Name = region.Key.Name,
        GlobalRevenue = g?.Revenue,
        GlobalRank = g?.Rank,
        UsaRevenue = u?.Revenue,
        UsaRank = u?.Rank,
        EuroRevenue = e?.Revenue,
        EuroRank = e?.Rank,
        ApacRevenue = a?.Revenue,
        ApacRank = a?.Rank,
    };
Comments