Merging two Datatables into a Matrix style Datatable.

[riltim]

Hi,

I have a situation where I need to take multiple DataTables and
convert them to a single Datatable which would be simple if I didn't
need to convert them into a matrix where like items will be combined.
For example say I have 3 DataTables like so:

Note all three datatables have the same fields. They are:
Qty, CatalogNo, Part, Description, length, width

DataTable1 with comma seperated field values:
1, Q123456, 01, Limit Switch, 0, 0
1, E654321, 02, 1/2" Bolt, 0, 0
8, F786543, 03, 3/4" Bolt, 0, 0
1, P967349, 04, 1/2" Stainless Plate, 13, 14
1, P967349, 05, 1/2" Stainless Plate, 123, 48

DataTable2 with comma seperated field values:
1, Q123456, 01, Limit Switch, 0, 0
1, E654321, 02, 1/2" Bolt, 0, 0
8, F786543, 03, 3/4" Bolt, 0, 0
1, P967322, 04, 1/2" Carbon Plate, 13, 14
1, P967322, 05, 1/2" Carbon Plate, 123, 48

DataTable3 with comma seperated field values:
1, Q123456, 01, Limit Switch, 0, 0
3, E654321, 02, 1/2" Bolt, 0, 0
8, F786543, 03, 3/4" Bolt, 0, 0
1, P967349, 04, 1/2" Stainless Plate, 13, 14
1, P967349, 05, 1/2" Stainless Plate, 123, 48


These tables would be then converted into a table with the fields:

Qty03, Qty02, Qty01, CatalogNo, Part, Description, length, width

And have the comma seperated field values of:
1, 1, 1, Q123456, 01, Limit Switch, 0, 0
3, 1, 1, E654321, 02, 1/2" Bolt, 0, 0
8, 8, 8, F786543, 03, 3/4" Bolt, 0, 0
1, 0, 1, P967349, 04, 1/2" Stainless Plate, 13, 14
0, 1, 0, P967322, 04, 1/2" Carbon Plate, 13, 14
1, 0, 1, P967349, 05, 1/2" Stainless Plate, 123, 48
0, 1, 0, P967322, 05, 1/2" Carbon Plate, 123, 48

I realize this isn't really clear but I hope someone can follow what
I'm trying to do. I'll take any help I can get.

Thanks,
Tim

[Plater]

Well I don't know of a "nice" way of doing it, only the long way.
Create a DataTable with your desired columns.
Loop through each DataTable, inserting (or updating) their values into that new DataTable.

EDIT:
Actually, I think you can do this with some fancy SQL, if you can call a SELECT statement to reference all three datatable at once and use a GROUP BY clause, you should be able to do it.

Something in the idea of this:

SELECT t1.qty as [QTY1], t2.qty as [QTY2], t3.qty as [QTY3], <"other categories"> FROM t1, t2, t3 GROUP BY <"whatever it is that you would use to combine entries">
 

[riltim]

Thanks for the reply. I figured I would have to go about it the long way to combine all this data. I would take the SQL approach but I'm not pulling this information from a database, I'm loading the table from an XML string I am reading and writing to a file.