1

I have a datatable output like shown below, I'm trying to sort / order the rows in a way so that the values of the column containing test, test1, test2 will not be repeated after each other.

Basicly I just want to "mix" the rows, so the same value of the column is not repeated (as far as possiple)

The image shows 4 columns out of the 21 in the datatable Link to image

Suren Grig
  • 75
  • 1
  • 1
  • 11
Nicklas Christensen
  • 21
  • 7

2 Answers2

0

Rango's answer is more concise, but since I worked on one, here it is. You can use GroupBy to group the items, and then add items to a new list from each group, in a loop:

static void Main(string[] args)
{
    var data = new List<string>
    {
        "test", "test", "test", "test", "test1", "test1",
        "test2", "test2", "test2", "test2", "test2", "test2",
    };

    var groups = data.GroupBy(x => x).OrderByDescending(x => x.Count());
    var maxGroupCount = groups.Max(g => g.Count());
    var orderedData = new List<string>();

    for (int i = 0; i < maxGroupCount; i++)
    {
        orderedData.AddRange(groups.Where(group => group.Count() > i)
            .Select(group => group.ElementAt(i)));
    }

    orderedData.ForEach(Console.WriteLine);

    GetKeyFromUser("\nDone! Press any key to exit...");
}

Output

enter image description here

Rufus L
  • 36,127
  • 5
  • 30
  • 43
  • That is the output that I want, but how can I apply this to a datatable, and maybe I forgot to mention that the datatable is dynmically filled from a mysql adapter, having 21 columns, but this is the output that I am looking for. Thanks in advance – Nicklas Christensen Dec 06 '18 at 17:17
  • Take a look at https://stackoverflow.com/questions/19076034/how-to-fill-a-datatable-with-listt – Rufus L Dec 06 '18 at 17:20
  • As I forgot to mention, the datatable has 21 columns and your sample as based on a single column (Again sorry for not being clear) – Nicklas Christensen Dec 06 '18 at 17:34
0

Here is a sample using LINQ to process it. This uses the ever forgotten two parameter lambda version of Select to get a position value for each type. Since no column names were shown, I called the first column type and the other column stands for the rest of the data.

var db = new[] {
    new { type = "test", other = 1 },
    new { type = "test", other = 2 },
    new { type = "test", other = 3 },
    new { type = "test", other = 4 },
    new { type = "test1", other = 5 },
    new { type = "test1", other = 6 },
    new { type = "test2", other = 7 },
    new { type = "test2", other = 8 },
    new { type = "test2", other = 9 },
    new { type = "test2", other = 10 },
};

var ans = db.GroupBy(d => d.type)
            .Select(dg => dg.Select((d, i) => new { d, i }))
            .SelectMany(dig => dig)
            .GroupBy(di => di.i)
            .SelectMany(dig => dig.Select(di => di.d));

Basically this is an idiom (now I want a cool name like Schwartzian transform) for pivoting an IEnumerable<IEnumerable>> which I then flatten.

I created an extension method to capture the pivot central idiom.

public static class IEnumerableIEnumerableExt {
    // Pivot IEnumerable<IEnumerable<T>> by grouping matching positions of each sub-IEnumerable<T>
    // src - source data
    public static IEnumerable<IEnumerable<T>> Pivot<T>(this IEnumerable<IEnumerable<T>> src) =>
        src.Select(sg => sg.Select((s, i) => new { s, i }))
            .SelectMany(sg => sg)
            .GroupBy(si => si.i)
            .Select(sig => sig.Select(si => si.s));

    public static DataTable ToDataTable(this IEnumerable<DataRow> src) {
        var ans = src.First().Table.Clone();
        foreach (var r in src)
            ans.ImportRow(r);
        return ans;
    }
}

With this extension method, the answer becomes:

var ans2 = db.GroupBy(d => d.type)
             .Pivot()
             .SelectMany(dg => dg);

And if the source is a DataTable, you can do this:

var ansdt = dt.AsEnumerable().GroupBy(r => r.Field<string>("type"))
              .Pivot()
              .SelectMany(rg => rg)
              .ToDataTable();

Since there isn't really an easy way to order or sort a DataTable, I added an extension method to convert the IEnumerable<DataRow> to a new DataTable.

NetMage
  • 26,163
  • 3
  • 34
  • 55
  • How does this sort / order the rows in the datatable to the needed output? I tried to fiddle with it in VS but it doesn't fit my needs I think. The datatable columns and rows are filled from a mysql dataadapter – Nicklas Christensen Dec 06 '18 at 19:41
  • @NicklasChristensen The Pivot converts a stream of streams to a new stream of streams, with the result sub-streams containing the nth element of each source stream. Did you try to run the sample code? This will easily work with a `DataTable`, just change the reference to `type` to a `.Field("fieldname")`. I added an example. – NetMage Dec 06 '18 at 20:26
  • @NicklasChristensen I realized you might want the answer as a `DataTable`, but sorting / processing `DataTable`s isn't really possible, so I added an extension method to convert the answer into a new `DataTable`. Really, working with `DataTable`s is somewhat obsolete when using a SQL Server. – NetMage Dec 06 '18 at 20:41
  • I would rep you if I could. Your solution was the closest I came, then I figured out that what I was actually looking for was not this. The answer is highly appreciated. Thank you! – Nicklas Christensen Dec 07 '18 at 12:18
  • @NicklasChristensen Glad to help! – NetMage Dec 07 '18 at 17:53