How to break down a DataTable into groups of rows(those with same traits under Column x)

Question

5.00/5 (1 vote)

See more:

Hi folks, i have a Datatable with one column of the table being 'PartNum(product part number)'.

I have used EntriesTable.DefaultView.Sort = "PartNum ASC"
to sort the whole Table, with respect to the information in this column. Now I'm just trying to further sort the table by grouping rows that have same info under that column together and do something to each of such groups. but I'm kinda stuck here.

I'm thinking of creating some sub-DataTables out of the original one and have these sub-tables to hold rows with same info under "PartNum".
Does anyone have ideas how to do that? maybe with DataReader or DataView , I think??

Thank you VJ Reddy for the solution. This does want I intended, with one exception though. For my application some of the PartNum starts with 0, i.e., "0239904". Both DataView and LINQ get rid of the 0 when they put the data in the subtables. But I need to keep the 0 there as it is.. I was using some datagrid watch the subtables:
DataGridView1.DataSource = subTables(0)
DataGridView2.DataSource = subTables(1)
DataGridView3.DataSource = SubTables(2)

Any ideas to get around the 0 problem?

Posted 3-May-12 11:24am

SandiegoSSD

Updated 11-May-12 5:29am

v2

Add a Solution

Comments

Maciej Los 3-May-12 18:16pm

Please, be more specific and show an example.
If your datatable is sorted by PartNum, the same PartNum are close to each other (imitation of group). Do you want to merge identical rows?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

VJ Reddy · Accepted Answer · 2012-05-03T15:18:00

The following code can be used to retrieve sub DataTables

VB

'Sample data to run the code
Dim parts As New DataTable()
parts.Columns.Add("PartNum", GetType(Integer), Nothing)
parts.Columns.Add("PartName", GetType(String), Nothing)
parts.Rows.Add(1, "OneTwo")
parts.Rows.Add(3, "ThreeOne")
parts.Rows.Add(1, "OneOne")
parts.Rows.Add(2, "TwoOne")
parts.Rows.Add(3, "ThreeTwo")
parts.Rows.Add(2, "TwoTwo")
parts.Rows.Add(1, "OneThree")

'Using DataView
parts.DefaultView.Sort = "PartNum"
Dim distinctParts As DataTable = parts.DefaultView.ToTable("DistinctParts", True, New String() {"PartNum"})
distinctParts.Dump()
Dim SubTables As New List(Of DataTable)()
For Each row As DataRow In distinctParts.Rows
    parts.DefaultView.RowFilter = String.Format("PartNum={0}", row("PartNum"))
    SubTables.Add(parts.DefaultView.ToTable(String.Format("Table{0}", row("PartNum"))))
Next

'Using LINQ
Dim subTables As List(Of DataTable) = parts.AsEnumerable().GroupBy( _
                Function(p) p.Field(Of Integer)("PartNum")) _
                .[Select](Function(p) p.CopyToDataTable()).ToList()

'Table 1
'1 OneTwo
'1 OneOne
'1 OneThree

'Table2
'3 ThreeOne
'3 ThreeTwo

'Table3
'2 TwoOne
'2 TwoTwo

Maciej Los · Accepted Answer · 2012-05-11T05:36:00

Solution 2

Just replace

VB

parts.Columns.Add("PartNum", GetType(Integer), Nothing)

with:

VB

parts.Columns.Add("PartNum", GetType(String), Nothing)

and the part numbers still will contain 0 (zero)

Posted 11-May-12 5:36am

Maciej Los

Comments

VJ Reddy 11-May-12 20:06pm

Good answer. 5!

Maciej Los 12-May-12 3:50am

Thank you, VJ ;)