Solved

VB.NET LINQ to Objects - Pivot Tables

Posted on 2008-10-24
3
2,718 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

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 …
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

813 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

16 Experts available now in Live!

Get 1:1 Help Now