Solved

VB.NET LINQ to Objects - Pivot Tables

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…

809 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