在C＃（linq？）中修复此数据的最简单方法是什么？

Given this example data (in .NET classes where Po, Sku, Qty are properties):

给出这个示例数据(在.NET类中,Po,Sku,Qty是属性):

PO, Sku, Qty
1,ABC,1
1,DEF,2
1,GHI,1
1,QWE,1
1,ASD,1
1,ZXC,5
1,ERT,1
2,QWE,1
2,ASD,11
2,ZXC,1
3,ERT,1
3,DFG,1
3,DFH,1
3,CVB,4
3,VBN,1
3,NMY,1

I need to transform it into a fixed column format, with a max of 5 SKUs per line (repeating the PO if needed for > 5):

我需要将其转换为固定列格式,每行最多5个SKU(如果需要,重复PO> 5):

PO, SkuA, QtyA, SkuB, QtyB, SkuC, QtyC, SkuD, QtyD, SkuE, QtyE
1, ABC, 1, DEF, 2, GHI, 1, QWE, 1, ASD, 1
1, ZXC, 5, ERT, 1, , , , , , 
2, QWE, 1, ASD, 11, ZXC, 1, , , , 
3, ERT, 1, DFG, 1, DFH, 1, CVB, 4, VBN, 1
3, NMY, 1, , , , , , , ,

Output can be CSV (which is what I'm outputting), or .NET classes - no matter there. Is there a simple way to do this in Linq by grouping by PO, then by counts of 5?

输出可以是CSV(这是我输出的),或.NET类 - 无论那里。有没有一种简单的方法可以在Linq中通过按PO分组,然后按5的数量进行分组?

EDIT: I have no control of over the destination format. And for anyone interested, it's VendorNet and VendorBridge that require this nonsense.

编辑:我无法控制目标格式。对于任何有兴趣的人来说,VendorNet和VendorBridge需要这些废话。

2 个解决方案

#1

Firstly, here's the query that will generate the correct hierarchy of objects. I'm using anonymous types but it's easy enough to change it to use your own proper classes.

首先,这是将生成正确的对象层次结构的查询。我正在使用匿名类型,但很容易将其更改为使用您自己的正确类。

var query = yourData
    .GroupBy
    (
        x => x.PO
    )
    .SelectMany
    (
        x => x.Select
        (
            (y, i) => new { y.PO, y.Sku, y.Qty, Key = i / 5 }
        )
    )
    .GroupBy
    (
        x => new { x.PO, x.Key }
    );

Using LINQ to create the CSV from the query results is bit of a hack, but it gets the job done. (The "benefit" of using LINQ is that you could chain the original query and the CSV generation into a single, massive statement, should you wish.)

使用LINQ从查询结果创建CSV有点像黑客,但它完成了工作。 (使用LINQ的“好处”是,如果您愿意,可以将原始查询和CSV生成链接到一个单独的大型语句中。)

IEnumerable<string> csvLines = query
    .Select
    (
        x => x.Aggregate
        (
            new { Count = 0, SB = new StringBuilder() },
            (a, y) => new
            {
                Count = a.Count + 1,
                SB = ((a.SB.Length == 0) ? a.SB.Append(y.PO) : a.SB)
                    .Append(", ").Append(y.Sku).Append(", ").Append(y.Qty)
            },
            a => a.SB.ToString() + string.Join(", , ", new string[6 - a.Count])
        )
    );

string csv = string.Join(Environment.NewLine, csvLines.ToArray());

In my opinion, creating the CSV without using LINQ makes the code much more readable:

在我看来,不使用LINQ创建CSV使代码更具可读性:

StringBuilder sb = new StringBuilder();
foreach (var group in query)
{
    int count = 0;
    foreach (var item in group)
    {
        if (count++ == 0)
        {
            sb.Append(item.PO);
        }
        sb.Append(", ").Append(item.Sku).Append(", ").Append(item.Qty);
    }
    while (count++ < 5)
    {
        sb.Append(", , ");
    }
    sb.Append(Environment.NewLine);
}

string csv = sb.ToString();

#2

Here you go. I didn't format the output the way you wanted. But this should give you an idea of how to pivot rows. Hope this helps :-)

干得好。我没有按照你想要的方式格式化输出。但是这应该让你知道如何转动行。希望这可以帮助 :-)

public class MyClass
{
        public int PO { get; set; }
        public String SKU { get; set; }
        public int Qty { get; set; }

        public static IEnumerable<MyClass> GetList()
        {
            return new List<MyClass>()
                       {
                           new MyClass {PO = 1, SKU = "ABC", Qty = 1},
                           new MyClass {PO = 1, SKU = "DEF", Qty = 2},
                           new MyClass {PO = 1, SKU = "GHI", Qty = 1},
                           new MyClass {PO = 1, SKU = "QWE", Qty = 1},
                           new MyClass {PO = 1, SKU = "ASD", Qty = 1},
                           new MyClass {PO = 1, SKU = "ZXC", Qty = 5},
                           new MyClass {PO = 1, SKU = "ERT", Qty = 1},
                           new MyClass {PO = 2, SKU = "QWE", Qty = 1},
                           new MyClass {PO = 2, SKU = "ASD", Qty = 1},
                           new MyClass {PO = 2, SKU = "ZXC", Qty = 5},
                       };
        }
 }

EDIT: I've fixed the query based on Luke's comment

编辑:我已根据卢克的评论修正了查询

var lQuery =
            MyClass.GetList()
                .GroupBy(pArg => pArg.PO)
                .Select(pArg => new
                   {
                       Test = pArg.Select((pArg1, pId) => 
                                          new {ID = (pId / 5), 
                                               pArg1.PO, pArg1.SKU, pArg1.Qty})
                                       .GroupBy(pArg1 => pArg1.ID)
                                       .Select(pArg1 => 
                                               pArg1.Aggregate(pArg.Key.ToString(),
                                                               (pSeed, pCur) => 
                                                               pSeed + pCur.SKU + ","))
                        });

#1