Solved

VB.NET LINQ to Objects - Pivot Tables

Posted on 2008-10-24
3
2,710 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
ID: 22799948
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
ID: 22810906
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
ID: 22812031
Not a problem, glad I was able to help.  ;=)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

947 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

21 Experts available now in Live!

Get 1:1 Help Now