Grouping dataset (in VB)

Question

User1787259644 posted

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´m using VB.

Hope you can help, thanks in advance.

Answer 1

User-492460945 posted

Hi Bjarnoe,

Firstly, you get the distinct values of grouping fields to a datatable using the below code.

dtGroup = dt.DefaultView.ToTable(true, "column1", "column2");

Next make nested looping with dtGroup at initial loop and dt at inner loop as below.

for (int i = 0; i < dtGroup.Rows.Count; i++)
        {
            for (int k = 0; k < dt.Rows.Count; k++)
            {
                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() &&
                    dtGroup.Rows[i]["Column2"].ToString() == dt.Rows[k]["Column2"].ToString())
                {
                    table += "<tr>" +
                    "<td>" + dt.Rows[k]["Column3"].ToString() + "</td>" +
                    "<td>" + dt.Rows[k]["Column4"].ToString() + "</td>" +
                    "<td>" + dt.Rows[k]["Column5"].ToString() + "</td>" +
                    "</tr>";
                }
            }
        }

The table string can be assigned to div to render in html.

Thanks,

RajeshV.

Answer 2

User1787259644 posted

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?

Answer 3

User-492460945 posted

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

Thanks,

RajeshV.

Answer 4

User36583972 posted

Hi Bjarnoe,

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

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

Answer 5

User1787259644 posted

r

Answer 6

User1787259644 posted

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

Thanks,

RajeshV.

Thank you for your reply, I will try it out.

Answer 7

User1787259644 posted

Hi Bjarnoe,

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

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

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.