Solved

VB.NET LINQ to Objects - Pivot Tables

Posted on 2008-10-24
3
2,737 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 63

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 63

Expert Comment

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

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

735 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