Solved

VB.NET LINQ to Objects - Pivot Tables

Posted on 2008-10-24
3
2,700 Views
Last Modified: 2013-11-11
I'm trying to create a pivot table view of data as you would get in excel but to be displayed in a ultrawingrid.

I have a collection class called PeopleToActivities.  This is a collection of PersonToActivity classes.
The PersonToActivity class has three methods that I'm interested in ActivityCode, FirstName and Cost.
The dynamic data looks as follows:
ActivityCode   FirstName   Cost
A1                   Bob             10
A2                   Bob             20
A3                   Bob             70
A1                   Sue             50
A5                   Sue             50

I'd like the data to be returned as follows:
           A1   A2  A3 A5
Bob    10    20   70
Sue    50                  50

The number of columns will need to be dynamically created as well as the rows.

I found the following post on here, however I couldn't get my head around the solution.
http://www.experts-exchange.com/Programming/Languages/.NET/LINQ/Q_23732487.html

If anyone could provide sample code using the above in VB.NET I would be very grateful.

Many thanks.


0
Comment
Question by:redoxsoft
  • 2
3 Comments
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
Comment Utility
Hi redoxsoft;

Below is code snippet showing how you can build the pivot table using Linq and a DataTable. I have not used the ultrawingrid control so not sure what methods are available but this should help getting to the solution.

Fernando

Imports System.Data
 

Public Class Form1
 

    Private PeopleToActivities As New List(Of PersonToActivity)()

    ' Have not worked with ultrawingrid so using DataTable to display in a DataGridView

    Private activityTable As New DataTable
 

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        ' Set up the in memory collection

        PeopleToActivities.Add(New PersonToActivity("A1", "Bob", "10"))

        PeopleToActivities.Add(New PersonToActivity("A2", "Bob", "20"))

        PeopleToActivities.Add(New PersonToActivity("A3", "Bob", "70"))

        PeopleToActivities.Add(New PersonToActivity("A1", "Sue", "50"))

        PeopleToActivities.Add(New PersonToActivity("A5", "Sue", "50"))

        ' Create the columns of the DataTable

        activityTable.Columns.Add("First Name", GetType(System.String))

        activityTable.Columns.Add("A1", GetType(System.String))

        activityTable.Columns.Add("A2", GetType(System.String))

        activityTable.Columns.Add("A3", GetType(System.String))

        activityTable.Columns.Add("A4", GetType(System.String))

        activityTable.Columns.Add("A5", GetType(System.String))

    End Sub
 

    Private Sub Button1_Click(ByVal sender As System.Object, _

        ByVal e As System.EventArgs) Handles Button1.Click
 

        ' Query to Group the records By FirstName

        Dim query = From act In PeopleToActivities _

                    Group By FirstName = (act.FirstName) Into Group
 

        ' Enumerate through the collection and fill the DataTable

        For Each group In query

            Dim dr As DataRow = activityTable.NewRow()

            dr("First Name") = group.FirstName

            For Each act In group.Group

                dr(act.ActivityCode) = act.Cost

            Next

            activityTable.Rows.Add(dr)

        Next
 

        DataGridView1.DataSource = activityTable
 

    End Sub
 

End Class
 

Public Class PersonToActivity
 

    Sub New(ByVal ac As String, ByVal fn As String, ByVal c As String)

        ActivityCode = ac

        FirstName = fn

        Cost = c

    End Sub
 

    Private _ActivityCode As String

    Public Property ActivityCode() As String

        Get

            Return _ActivityCode

        End Get

        Set(ByVal value As String)

            _ActivityCode = value

        End Set

    End Property
 

    Private _FirstName As String

    Public Property FirstName() As String

        Get

            Return _FirstName

        End Get

        Set(ByVal value As String)

            _FirstName = value

        End Set

    End Property
 

    Private _Cost As String

    Public Property Cost() As String

        Get

            Return _Cost

        End Get

        Set(ByVal value As String)

            _Cost = value

        End Set

    End Property
 

End Class

Open in new window

0
 
LVL 1

Author Comment

by:redoxsoft
Comment Utility
Hi Fernando,

The ultrawingrid uses a binding source so a datatable it perfect.  The solution worked well, thank you very much.

Nathan
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
Not a problem, glad I was able to help.  ;=)
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now