Bambuk Bambuk - 4 months ago 10
SQL Question

Group by results which are in the same order

I have a problem with grouping my resultset.

I need to group my results on

Tab_1_colName
and the (different part) the order of the results of
Tab_2_Order
if they have the same
Tab_2_Ids
.

So i have a first resultset, which looks like this:

Tab_1_colName Tab_2_colName Tab_2_Id Tab_2_Order
Name_1 Type_1 16 10
Name_1 Type_2 17 20
Name_1 Type_3 18 30
Name_2 Type_1 16 10
Name_2 Type_2 17 20
Name_2 Type_3 18 30
Name_3 Type_1 16 400
Name_3 Type_2 17 500
Name_3 Type_3 18 600
Name_4 Type_1 16 10
Name_4 Type_3 18 20
Name_4 Type_2 17 30


The result which i need looks like this:

Group1 => (Name_1 - Name_3) with the IDs in this order (16, 17, 18),
Group2 => (Name_4) with the IDs in this order (16, 18, 17)


I have no Idee how to achive this. I tried to group it directly on the DB, but there i stuck on

SELECT
Tab_1_colName, Tab_2_Id
FROM
Tab_1,
Tab_2
WHERE
Tab_1_othderID = Tab_2_othderID
group by
Tab_2_Id, Tab_1_colName


My second idea was to try it with linq, but there i stuck even fast as with the pure sql try

dt.Rows.OfType<DataRow>().OrderBy(x => x["Tab_2_Order"]).GroupBy(r => r["Tab_2_Id"])


Any ideas how to solve my problem?

EDIT:

Expected Result:

First Row: Name_1 with the Tab_2_Ids order by tab_2_order.

Second Row: Name_4 with the Tab_2_Ids order by tab_2_order.

(Name_2 and Name_3 should be in the same group like Name_1 because they have the same Tab_2_Ids in the same order)

Answer

Solved my Problem. With the great help of another user, who unfortunately deleted his anwser before i get it to run. :(

A big thank you to you! :)

Anyway here is my solution:

var dtRows = dt.Rows.OfType<DataRow>();

var rows = dtRows.GroupBy(r =>
            String.Join(",", dtRows.Where(q => q[0] == r[0]).OrderBy(o => o[3]).Select(s => s[0]).ToArray()),
            x => x[2],
            (key, g) => new Tuple<string, string[]>(key, g.Where(s => s != null).Select(s => s.ToString()).ToArray().Distinct().ToArray())
          ).ToList();

List<Tuple<string, string[], List<string>>> rowsToWrite = new List<Tuple<string, string[], List<string>>>();

foreach (var row in rows)
{
    if (!rowsToWrite.Any(x => x.Item2.SequenceEqual(row.Item2)))
        rowsToWrite.Add(Tuple.Create(row.Item1, row.Item2, new List<string>()));
    else
        rowsToWrite.Select(x => x).Where(x => x.Item2.SequenceEqual(row.Item2)).First().Item3.Add(row.Item1);
}