在c#中旋转多个列
本文关键字：旋转 | 更新日期: 2023-09-27 18:03:24
我有一个c#对象列表，我需要pivot和transform。但我不确定如何得到想要的结果。下面的代码讨论了结构、数据和期望的结果。我知道，我需要先对数据进行分组，然后进行pivot。但我不知道如何实现它。
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; }
}
您可以采用以下方法:
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,
};
如果在没有区域的情况下需要null值，则需要更改模型以能够支持它们。
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,
};
当你不使用c# 6.0
var pivoted =
from x in revList.ToLookup(d => new { d.Id, d.Name })
let g = x.SingleOrDefault(d => d.Region == "Global") ?? new Rev()
let u = x.SingleOrDefault(d => d.Region == "USA") ?? new Rev()
let e = x.SingleOrDefault(d => d.Region == "Euro") ?? new Rev()
let a = x.SingleOrDefault(d => d.Region == "APAC") ?? new Rev()
select new RevExtended
{
Id = x.Key.Id,
Name = x.Key.Name,
GlobalRevenue = g.Revenue == null ? null : g.Revenue,
GlobalRank = g.Rank == null ? null : g.Rank,
USARevenue = u.Revenue == null ? null : u.Revenue,
USARank = u.Rank == null ? null : u.Rank,
EuroRevenue = e.Revenue == null ? null: e.Revenue,
EuroRank = e.Rank == null ? null : e.Rank,
ApacRevenue = a.Revenue == null ? null : a.Revenue,
ApacRank = a.Rank == null ? null : a.Rank,
};
@OpenStack，你有一个针对6.0之前的c#用户的答案，基于接受答案的最后一部分。你举的例子不太对。有两个问题:
- 你的例子将总是有一个Rev对象，所以对象引用永远不会为空。
- 如果您允许null(如预期的那样)，赋值逻辑不会保护您免受null的影响。
以下是c# 6.0之前版本的可接受答案:
var pivoted =
from x in revList.ToLookup(d => new { d.Id, d.Name })
let g = x.SingleOrDefault(d => d.Region == "Global")
let u = x.SingleOrDefault(d => d.Region == "USA")
let e = x.SingleOrDefault(d => d.Region == "Euro")
let a = x.SingleOrDefault(d => d.Region == "APAC")
select new RevExtended
{
Id = x.Key.Id,
Name = x.Key.Name,
GlobalRevenue = g == null ? null : g.Revenue,
GlobalRank = g == null ? null : g.Rank,
USARevenue = u == null ? null : u.Revenue,
USARank = u == null ? null : u.Rank,
EuroRevenue = e == null ? null: e.Revenue,
EuroRank = e == null ? null : e.Rank,
ApacRevenue = a == null ? null : a.Revenue,
ApacRank = a == null ? null : a.Rank,
};
如果你同意，你可以更新你的答案，使它是正确的(复制和粘贴我的如果你喜欢)，我将删除这一个。