Hi,
I guess this should be fairly simple, but I haven't been able to find a solution.
I'm retreiving orderlines from a db table, that I need to group by the OrderId. A simplified scheme of the table looks like this:
OrderId
OrderLineTitle
OrderLinePrice
PurchaseDate
OrderTotal
5566
Blue sweater
99.99
2018.07.07
199.99
5566
Red sweater
100
2018.07.07
199.99
7788
Yellow sweater
88.88
2018.06.06
88.88
9900
Green sweater
77.77
2018.05.05
177.77
9900
Brown sweater
100.00
2018.05.05
177.77
What I need is this output:
5566
2018.07.07
199.99
Blue sweater
99.99
Red sweater
100
7788
2018.06.06
88.88
Yellow sweater
88.88
9900
2018.05.05
177.77
Green sweater
77.77
Brown sweater
100.00
So basically I need to group the data by OrderId for each order and only display one row with the common values for each of the orderlines, e.g. PurchaseDate and OrderTotal.
And below that I need to display all the orderlines in the order and display the OrderLineTitles.
I woukd suggest you to convert datareader to datatable in the below way.
Dim dr As SqlDataReader = cmd.ExecuteReader()
Dim dt As DataTable = New DataTable()
dt.Load(dr)
Your code in VB would be like below.
Dim dt As DataTable = New DataTable
Dim dtGroup As DataTable = New DataTable
Dim table As String = string.Empty
Dim i As Integer = 0
,k = 0
i = 0
Do While (i < dtGroup.Rows.Count)
k = 0
Do While (k < dt.Rows.Count)
table = (table + ("<tr>" + ("<td>" _
+ (dtGroup.Rows(i)("Column1").ToString + ("</td>" + ("<td colspan='2'>" _
+ (dtGroup.Rows(i)("Column2").ToString + ("</td>" + "</tr>"))))))))
If ((dtGroup.Rows(i)("Column1").ToString = dt.Rows(k)("Column1").ToString) _
AndAlso (dtGroup.Rows(i)("Column2").ToString = dt.Rows(k)("Column2").ToString)) Then
table = (table + ("<tr>" + ("<td>" _
+ (dt.Rows(k)("Column3").ToString + ("</td>" + ("<td>" _
+ (dt.Rows(k)("Column4").ToString + ("</td>" + ("<td>" _
+ (dt.Rows(k)("Column5").ToString + ("</td>" + "</tr>")))))))))))
End If
k = (k + 1)
Loop
i = (i + 1)
Loop
You can use the Group by Functionality on a DataSet(DataTable)
You can refer the following code.
Dim team As New List(Of Staff)
team.Add(New Staff With {.OrderId = 5566, .OrderLineTitle = "Blue sweater", .OrderLinePrice = "99.99", .PurchaseDate = "2018.07.07", .OrderTotal = "199.99"})
team.Add(New Staff With {.OrderId = 5566, .OrderLineTitle = "Red sweater", .OrderLinePrice = "100", .PurchaseDate = "2018.07.07", .OrderTotal = "199.99"})
team.Add(New Staff With {.OrderId = 7788, .OrderLineTitle = "Yellow sweater", .OrderLinePrice = "88.88", .PurchaseDate = "2018.06.06", .OrderTotal = "88.88"})
team.Add(New Staff With {.OrderId = 9900, .OrderLineTitle = "Green sweater", .OrderLinePrice = "77.77", .PurchaseDate = "2018.05.05", .OrderTotal = "177.77"})
team.Add(New Staff With {.OrderId = 9900, .OrderLineTitle = "Brown sweater", .OrderLinePrice = "100", .PurchaseDate = "2018.05.05", .OrderTotal = "177.77"})
Dim summary1 = From member In team
Group member By keys = New With {Key member.OrderId, Key member.PurchaseDate, Key member.OrderTotal}
Into Group
Select New With {
Key .Dates = keys,
.value = Group
}
For Each Info In summary1
Response.Write(String.Format("{0}__{1}__{2}: <br/>", Info.Dates.OrderId, Info.Dates.PurchaseDate, Info.Dates.OrderTotal))
For Each dr As Staff In Info.value
Response.Write(String.Format("{0} {1} <br/>", dr.OrderLineTitle, dr.OrderLinePrice))
Next
Next
ASP.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
You can use the Group by Functionality on a DataSet(DataTable)
You can refer the following code.
Dim team As New List(Of Staff)
team.Add(New Staff With {.OrderId = 5566, .OrderLineTitle = "Blue sweater", .OrderLinePrice = "99.99", .PurchaseDate = "2018.07.07", .OrderTotal = "199.99"})
team.Add(New Staff With {.OrderId = 5566, .OrderLineTitle = "Red sweater", .OrderLinePrice = "100", .PurchaseDate = "2018.07.07", .OrderTotal = "199.99"})
team.Add(New Staff With {.OrderId = 7788, .OrderLineTitle = "Yellow sweater", .OrderLinePrice = "88.88", .PurchaseDate = "2018.06.06", .OrderTotal = "88.88"})
team.Add(New Staff With {.OrderId = 9900, .OrderLineTitle = "Green sweater", .OrderLinePrice = "77.77", .PurchaseDate = "2018.05.05", .OrderTotal = "177.77"})
team.Add(New Staff With {.OrderId = 9900, .OrderLineTitle = "Brown sweater", .OrderLinePrice = "100", .PurchaseDate = "2018.05.05", .OrderTotal = "177.77"})
Dim summary1 = From member In team
Group member By keys = New With {Key member.OrderId, Key member.PurchaseDate, Key member.OrderTotal}
Into Group
Select New With {
Key .Dates = keys,
.value = Group
}
For Each Info In summary1
Response.Write(String.Format("{0}__{1}__{2}: <br/>", Info.Dates.OrderId, Info.Dates.PurchaseDate, Info.Dates.OrderTotal))
For Each dr As Staff In Info.value
Response.Write(String.Format("{0} {1} <br/>", dr.OrderLineTitle, dr.OrderLinePrice))
Next
Next
Thank you, I will try it out. I have been looking at Linq but haven't found anything yet that was as well explained as your example, so thank you for that.
None
0 Points
5 Posts
Grouping dataset (in VB)
Jul 09, 2018 01:50 PM|Bjarnoe|LINK
Hi,
I guess this should be fairly simple, but I haven't been able to find a solution.
I'm retreiving orderlines from a db table, that I need to group by the OrderId. A simplified scheme of the table looks like this:
What I need is this output:
So basically I need to group the data by OrderId for each order and only display one row with the common values for each of the orderlines, e.g. PurchaseDate and OrderTotal.
And below that I need to display all the orderlines in the order and display the OrderLineTitles.
I´m using VB.
Hope you can help, thanks in advance.
Participant
1644 Points
792 Posts
Re: Grouping dataset (in VB)
Jul 09, 2018 03:06 PM|rajesh93180|LINK
Hi Bjarnoe,
Firstly, you get the distinct values of grouping fields to a datatable using the below code.
Next make nested looping with dtGroup at initial loop and dt at inner loop as below.
The table string can be assigned to div to render in html.
Thanks,
RajeshV.
Mark as answer if you find this post helpful.
None
0 Points
5 Posts
Re: Grouping dataset (in VB)
Jul 09, 2018 03:22 PM|Bjarnoe|LINK
Thanks for your reply, but I´m using VB, are you able to convert it?
Also, is it possible to do it using a DataReader and While instead of a datatable?
Participant
1644 Points
792 Posts
Re: Grouping dataset (in VB)
Jul 09, 2018 03:31 PM|rajesh93180|LINK
I woukd suggest you to convert datareader to datatable in the below way.
Your code in VB would be like below.
Thanks,
RajeshV.
Mark as answer if you find this post helpful.
Star
11464 Points
2439 Posts
Re: Grouping dataset (in VB)
Jul 10, 2018 07:32 AM|Yohann Lu|LINK
Hi Bjarnoe,
You can use the Group by Functionality on a DataSet(DataTable)
You can refer the following code.
Group By Clause (Visual Basic)
https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/queries/group-by-clause
Implementing Simple SQL Group by Functionality with LINQ Technology on a DataSet
https://www.codeproject.com/Articles/28132/Implementing-Simple-SQL-Group-by-Functionality-wit
Best Regards,
Yong Lu
None
0 Points
5 Posts
Re: Grouping dataset (in VB)
Jul 10, 2018 09:54 AM|Bjarnoe|LINK
r
None
0 Points
5 Posts
Re: Grouping dataset (in VB)
Jul 10, 2018 09:55 AM|Bjarnoe|LINK
Thank you for your reply, I will try it out.
None
0 Points
5 Posts
Re: Grouping dataset (in VB)
Jul 10, 2018 09:58 AM|Bjarnoe|LINK
Thank you, I will try it out. I have been looking at Linq but haven't found anything yet that was as well explained as your example, so thank you for that.