I have seen this request a lot on the web and I have yet to see an answer
for VB.Net. The answers these people receive amount to "Why" or
"You can't", in some cases some other BS code that is not what was
asked for. Some instances I found a C# snippet that might have solved it,
however I do not do C# and needed a VB.Net solution.
My question is: Why not simply provide a sample of the requested code if you
are going to bother to reply to the question? Otherwise why not simply say you are
too damn lazy to work it out? If people ask the question it means they have a
need for the answer, some may be able to stray away from what they initially
wanted, but not everyone can. So why give them a BS answer? Most annoying.
You can Group By on a Datatable using multiple columns, however you have to
code it and it is not a part of the Datatable. Thus all the questions on how to
do it and the purpose of this post.
At any rate here is my solution, it may not be the best nor very elegant,
but it answers the friggin question and it worked to serve my purpose without
using the BS most people received as an answer.
Groups on One column by default and allows up to 5 columns. It does add a
Count Column as a bonus.
You pick and modify as needed.
Inputs:
myDataTable = Your Datatable
5 = Number of fields to use as Integer
strx(x) = Column Names as string
The following spits it out into an HTML Table.
Response.Write(Grouping2(myDataTable, 5, strx(0), strx(1), strx(2), strx(3), strx(4)))
My need was a dynamic selection of Columns up to 5. After some research and head scratching I came up with this:
Function Grouping(ByVal dt As DataTable, ByVal Opts As Integer, ByVal str1 As String, Optional ByVal str2 As String = "", _
Optional ByVal str3 As String = "", Optional ByVal str4 As String = "", Optional ByVal str5 As String = "") As String
'Row Count
Dim i As Integer = 0
'Iteration Count
Dim x As Integer = 0
'Columns
Dim CurColumn1 As String = ""
Dim CurColumn2 As String = ""
Dim CurColumn3 As String = ""
Dim CurColumn4 As String = ""
Dim CurColumn5 As String = ""
Dim result As New StringBuilder()
Dim CurString As String = ""
Dim dv As New DataView(dt)
Select Case Opts
Case 0
CurString = str1 & " ASC"
Case 1
CurString = str1 & " ASC" & "," & str2 & " ASC"
Case 2
CurString = str1 & " ASC" & "," & str2 & " ASC" & "," & str3 & " ASC"
Case 3
CurString = str1 & " ASC" & "," & str2 & " ASC" & "," & str3 & " ASC" & "," & str4 & " ASC"
Case 4
CurString = str1 & " ASC" & "," & str2 & " ASC" & "," & str3 & " ASC" & "," & str4 & " ASC" & "," & str5 & " ASC"
End Select
dv.Sort = CurString
Dim r As DataRowCollection = dv.ToTable.Rows
CurString = ""
' Set Field names to append given Opts Integer
result.AppendLine("<table cellspacing='0' rules='all' border='1' style='font-size:12pt;border-collapse:collapse;'>")
Select Case Opts
Case 0
CurString = str1
result.AppendFormat("<tr style='background-color:LightGrey;font-weight:bold;'><td>{0}</td>", CurString).AppendLine()
CurString = "COUNT"
result.AppendFormat("<td>{0}</td></tr>", CurString).AppendLine()
Case 1
CurString = str1
result.AppendFormat("<tr style='background-color:LightGrey;font-weight:bold;'><td>{0}</td>", CurString).AppendLine()
CurString = str2
result.AppendFormat("<td>{0}</td>", CurString).AppendLine()
CurString = "COUNT"
result.AppendFormat("<td>{0}</td></tr>", CurString).AppendLine()
Case 2
CurString = str1
result.AppendFormat("<tr style='background-color:LightGrey;font-weight:bold;'><td>{0}</td>", CurString).AppendLine()
CurString = str2
result.AppendFormat("<td>{0}</td>", CurString).AppendLine()
CurString = str3
result.AppendFormat("<td>{0}</td>", CurString).AppendLine()
CurString = "COUNT"
result.AppendFormat("<td>{0}</td></tr>", CurString).AppendLine()
Case 3
CurString = str1
result.AppendFormat("<tr style='background-color:LightGrey;font-weight:bold;'><td>{0}</td>", CurString).AppendLine()
CurString = str2
result.AppendFormat("<td>{0}</td>", CurString).AppendLine()
CurString = str3
result.AppendFormat("<td>{0}</td>", CurString).AppendLine()
CurString = str4
result.AppendFormat("<td>{0}</td>", CurString).AppendLine()
CurString = "COUNT"
result.AppendFormat("<td>{0}</td></tr>", CurString).AppendLine()
Case 4
CurString = str1
result.AppendFormat("<tr style='background-color:LightGrey;font-weight:bold;'><td>{0}</td>", CurString).AppendLine()
CurString = str2
result.AppendFormat("<td>{0}</td>", CurString).AppendLine()
CurString = str3
result.AppendFormat("<td>{0}</td>", CurString).AppendLine()
CurString = str4
result.AppendFormat("<td>{0}</td>", CurString).AppendLine()
CurString = str5
result.AppendFormat("<td>{0}</td>", CurString).AppendLine()
CurString = "COUNT"
result.AppendFormat("<td>{0}</td></tr>", CurString).AppendLine()
End Select
'Append Field names to the result
'result.AppendFormat("{0}", vbCrLf).AppendLine()
CurString = ""
While (i < r.Count)
'Default Column
CurColumn1 = r(i)(str1)
'x records the Number of Occurences
'Zero out x prior to the next iteration
x = 0
'Opts is a value 0-4 Representing the 1 to 5 Fields for the Group By
'Each Case performs the same function with one more column than the previous Case
Select Case Opts
Case 0
'iterate through the records with our Criteria
'Set Column Values (We only use the Default Column here)
CurColumn2 = ""
CurColumn3 = ""
CurColumn4 = ""
CurColumn5 = ""
While i < r.Count AndAlso CurColumn1 = r(i)(str1)
'iterate through the records
While i < r.Count AndAlso CurColumn1 = r(i)(str1)
i += 1 'Next Record
x += 1 'Record Count Per
'Set our string Value and Number of Iterations
CurString = "<tr><td>" & r(i - 1)(str1) & "</td><td>" & x & "</td></tr>"
End While
End While
'Set our string Value and Number of Iterations
result.AppendFormat("{0}", CurString).AppendLine()
result.AppendFormat("{0}", vbCrLf).AppendLine()
Case 1
'iterate through the records with our Criteria
'Set Column Values
CurColumn2 = r(i)(str2)
CurColumn3 = ""
CurColumn4 = ""
CurColumn5 = ""
While i < r.Count AndAlso CurColumn1 = r(i)(str1) AndAlso CurColumn2 = r(i)(str2)
'iterate through the records
While i < r.Count AndAlso CurColumn1 = r(i)(str1) AndAlso CurColumn2 = r(i)(str2)
i += 1 'Next Record: same user, role '
x += 1 'Record Count Per
End While
CurString = "<tr><td>" & r(i - 1)(str1) & "</td><td>" & r(i - 1)(str2) & "</td><td>" & x & "</td></tr>"
End While
'Set our string Value and Number of Iterations
result.AppendFormat("{0}", CurString).AppendLine()
result.AppendFormat("{0}", vbCrLf).AppendLine()
Case 2
'iterate through the records with our Criteria
'Set Column Values
CurColumn2 = r(i)(str2)
CurColumn3 = r(i)(str3)
CurColumn4 = ""
CurColumn5 = ""
While i < r.Count AndAlso CurColumn1 = r(i)(str1) AndAlso CurColumn2 = r(i)(str2) AndAlso CurColumn3 = r(i)(str3)
'iterate through the records
While i < r.Count AndAlso CurColumn1 = r(i)(str1) AndAlso CurColumn2 = r(i)(str2) AndAlso CurColumn3 = r(i)(str3)
i += 1 'Next Record
x += 1 'Record Count Per group
End While
End While
'Set our string Value and Number of Iterations
CurString = "<tr><td>" & r(i - 1)(str1) & "</td><td>" & r(i - 1)(str2) & "</td><td>" & r(i - 1)(str3) & "</td><td>" & x & "</td></tr>"
result.AppendFormat("<tr><td>{0}</td></tr>", CurString).AppendLine()
result.AppendFormat("{0}", vbCrLf).AppendLine()
Case 3
'iterate through the records with our Criteria
'Set Column Values
CurColumn2 = r(i)(str2)
CurColumn3 = r(i)(str3)
CurColumn4 = r(i)(str4)
CurColumn5 = ""
While i < r.Count AndAlso CurColumn1 = r(i)(str1) AndAlso CurColumn2 = r(i)(str2) AndAlso CurColumn3 = r(i)(str3) AndAlso CurColumn4 = r(i)(str4)
'iterate through the records
While i < r.Count AndAlso CurColumn1 = r(i)(str1) AndAlso CurColumn2 = r(i)(str2) AndAlso CurColumn3 = r(i)(str3) AndAlso CurColumn4 = r(i)(str4)
i += 1 'Next Record
x += 1 'Record Count Per Group
End While
End While
'Set our string Value and Number of Iterations
CurString = "<tr><td>" & r(i - 1)(str1) & "</td><td>" & r(i - 1)(str2) & "</td><td>" & r(i - 1)(str3) & "</td><td>" & r(i - 1)(str4) & "</td><td>" & x & "</td></tr>"
result.AppendFormat("<tr><td>{0}</td></tr>", CurString).AppendLine()
result.AppendFormat("{0}", vbCrLf).AppendLine()
Case 4
'iterate through the records with our Criteria
'Set Column Values
CurColumn2 = r(i)(str2)
CurColumn3 = r(i)(str3)
CurColumn4 = r(i)(str4)
CurColumn5 = r(i)(str5)
While i < r.Count AndAlso CurColumn1 = r(i)(str1) AndAlso CurColumn2 = r(i)(str2) AndAlso CurColumn3 = r(i)(str3) AndAlso CurColumn4 = r(i)(str4) AndAlso CurColumn5 = r(i)(str5)
'iterate through the records
While i < r.Count AndAlso CurColumn1 = r(i)(str1) AndAlso CurColumn2 = r(i)(str2) AndAlso CurColumn3 = r(i)(str3) AndAlso CurColumn4 = r(i)(str4) AndAlso CurColumn5 = r(i)(str5)
i += 1 'Next Record
x += 1 'Record Count Per Group
End While
End While
'Set our string Value and Number of Iterations
CurString = "<tr><td>" & r(i - 1)(str1) & "</td><td>" & r(i - 1)(str2) & "</td><td>" & r(i - 1)(str3) & "</td><td>" & r(i - 1)(str4) & "</td><td>" & r(i - 1)(str5) & "</td><td>" & x & "</td></tr>"
result.AppendFormat("<tr><td>{0}</td></tr>", CurString).AppendLine()
result.AppendFormat("{0}", vbCrLf).AppendLine()
End Select
' result.AppendFormat("{0}", CurString).AppendLine()
End While
' Response.Write(Opts & "<BR>")
' Response.Write("<br><br>grouping: " & i & " " & result.ToString)
result.AppendLine("</table>").AppendLine()
Grouping = result.ToString
CurString = ""
CurColumn1 = ""
CurColumn2 = ""
CurColumn3 = ""
CurColumn4 = ""
CurColumn5 = ""
End Function
Reference
asp.net, datatable, groupby, multiple columns