Created
July 1, 2020 23:27
-
-
Save karenpayneoregon/fd7ecfaf6e66307fe9277070ccc9b6ec to your computer and use it in GitHub Desktop.
Group by two DataColumns
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Imports System.Text | |
''' <summary> | |
''' Requires | |
''' 1 Button named DistinctGroupButton | |
''' 1 DataGridView named DataGridView1 | |
''' </summary> | |
Public Class Form1 | |
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load | |
Dim dt As New DataTable With {.TableName = "MyTable"} | |
dt.Columns.Add(New DataColumn With {.ColumnName = "Identifier", .DataType = GetType(Integer), | |
.AutoIncrement = True, .AutoIncrementSeed = 1}) | |
dt.Columns.Add(New DataColumn With {.ColumnName = "Priority", .DataType = GetType(String)}) | |
dt.Columns.Add(New DataColumn With {.ColumnName = "Status", .DataType = GetType(String)}) | |
dt.Rows.Add(New Object() {Nothing, "Low", "New"}) | |
dt.Rows.Add(New Object() {Nothing, "High", "Old"}) | |
dt.Rows.Add(New Object() {Nothing, "Low", "New"}) | |
dt.Rows.Add(New Object() {Nothing, "Medium", "Old"}) | |
dt.Rows.Add(New Object() {Nothing, "Low", "New"}) | |
dt.Rows.Add(New Object() {Nothing, "Low", "New"}) | |
dt.Rows.Add(New Object() {Nothing, "High", "Old"}) | |
dt.Rows.Add(New Object() {Nothing, "Medium", "New"}) | |
dt.Rows.Add(New Object() {Nothing, "Medium", "Old"}) | |
dt.Rows.Add(New Object() {Nothing, "Low", "Old"}) | |
DataGridView1.DataSource = dt | |
End Sub | |
Private Sub DistinctGroupButton_Click(sender As Object, e As EventArgs) Handles DistinctGroupButton.Click | |
Dim query = DataGridView1.Rows.OfType(Of DataGridViewRow)(). | |
Where(Function(row) Not row.IsNewRow). | |
GroupBy(Function(row) | |
Return New With {Key .Priority = row.Cells("Priority").Value, | |
Key .Status = row.Cells("Status").Value} | |
End Function). | |
Select(Function(group) New With | |
{ | |
Key .Value = group.Key, | |
Key .Count = group.Count(), | |
Key .Rows = group.ToList() | |
} | |
). | |
OrderByDescending(Function(x) x.Count). | |
ToList | |
Dim sb As New StringBuilder | |
For Each item In query | |
If item.Count > 1 Then | |
sb.AppendLine($"{item.Value.Priority},{item.Value.Status} count {item.Count}") | |
For Each row In item.Rows | |
sb.AppendLine($" row: {row.Index}") | |
Next | |
End If | |
Next | |
TextBox1.Text = sb.ToString() | |
End Sub | |
End Class |
Thank you so much! I appreciate your help
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I'm going to be busy for a while so I suggest taking time with setting a breakpoint and inspecting what is being returned to learn than adapt as needed. Of course you can also asking in Stackoverflow or Microsoft Q&A forums.