How to combine similar rows in a Datatable(the rows only differ by one column)?

Question

0.00/5 (No votes)

See more:

I have three sub tables that I want to process. For each I want to combine the rows, as they are only different by contents in the second column(I want to do the same to the fourth column, later):

'Sub table 1

xx|C201 |02300877 |Samsung |….<br />
xx|C201 |02300877 |Toshiba |….<br />
xx|C213 |02300877 |Samsung<br />
xx|C213 |02300877 |Toshiba<br />
xx|C606 |02300877 |Samsung<br />
xx|C606 |02300877 |Toshiba

'Sub table 2

xx| C303 |02301163<br />
xx| C305 |02301163<br />
xx|C712 |02301163

' Sub table 3

xx|C207 |02301165 |....<br />
xx|C209 |02301165 |….<br />
xx|C708 |02301165

After the combining's done I want to put these back together to a single Datatable that would look like this:

xx| C201,C213,C606,C619 |02300877<br />
xx| C303, C305,C712 |02301163<br />
xx| C207, C209, C708 |02301165<br />

Now the second row would hold all different "Cxxx" information that appeared in the subtables. I'm fairly noob to vb and I'd appreciate some sample codes that could do the "combine rows in datatable" part and probably the final aggregation part. Any advice's appreciated. Thank you guys.

p.s. For the fourth column, Manufacturer information , I want to do the same and I'd probably get something like this for the final table:

xx| C201,C213,C606,C619 |02300877 | Samsung<br />
xx| C201,C213,C606,C619 |02300877 | Toshiba<br />
xx| C303, C305,C712 |02301163<br />
xx| C207, C209, C708 |02301165

[EDIT]Code tags added - LOSMAC[/EDIT]

Posted 8-May-12 9:01am

SandiegoSSD

Updated 8-May-12 9:47am

Maciej Los

v3

Add a Solution

2 solutions

Solution 2

Hi use sql commands to select data from each table
then use data reader to read data from tables and combining field2 datas (or use move first and then in a loop use movenext satatement by combining field2 datas for each record)and then insert them to final table
it is similar for the other tables
excuse me but i dont know what type of database you are using
Good Luck

Posted 8-May-12 10:07am

mohamadreza mokhtari

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)

Maciej Los · Accepted Answer · 2012-05-08T09:51:00

Take a look here: need sql query for some action: rows into single column depending on other column[^]

If you need an example, you need to put columns and tables names.

[EDIT]
I've done an example. How to test it?

1) Copy code below.
2) Open MS SQL Server Management Studio and click "New query" button
3) Paste code from clipboard
4) Push "Execute" button

SQL

IF NOT OBJECT_ID(N'#Products') IS NULL DROP TABLE #Products

CREATE TABLE #Products (Category NVARCHAR(10), ShortName NVARCHAR(10), CodeId NVARCHAR(30), Manufacturer NVARCHAR(50))

INSERT INTO #Products (Category, ShortName, CodeId, Manufacturer)
	VALUES('xx', 'C201', '02300877','Samsung')
INSERT INTO #Products (Category, ShortName, CodeId, Manufacturer)
	VALUES('xx', 'C201','02300877' ,'Toshiba')
INSERT INTO #Products (Category, ShortName, CodeId, Manufacturer)
	VALUES('xx', 'C213', '02300877','Samsung')
INSERT INTO #Products (Category, ShortName, CodeId, Manufacturer)
	VALUES('xx', 'C213', '02300877','Toshiba')
INSERT INTO #Products (Category, ShortName, CodeId, Manufacturer)
	VALUES('xx', 'C606', '02300877','Samsung')
INSERT INTO #Products (Category, ShortName, CodeId, Manufacturer)
	VALUES('xx', 'C606', '02300877','Toshiba')
INSERT INTO #Products (Category, ShortName, CodeId, Manufacturer)
	VALUES('xx', 'C303', '02301163','Toshiba')
INSERT INTO #Products (Category, ShortName, CodeId, Manufacturer)
	VALUES('xx', 'C305', '02301163','Toshiba')
INSERT INTO #Products (Category, ShortName, CodeId, Manufacturer)
	VALUES('xx', 'C712', '02301163','Samsung')
INSERT INTO #Products (Category, ShortName, CodeId, Manufacturer)
	VALUES('xx', 'C207', '02301165','Samsung')
INSERT INTO #Products (Category, ShortName, CodeId, Manufacturer)
	VALUES('xx', 'C209', '02301165','Philips')
INSERT INTO #Products (Category, ShortName, CodeId, Manufacturer)
	VALUES('xx', 'C708', '02301165','Philips')

SELECT  t1.[Category], STUFF( (SELECT ',' + [ShortName] AS 'text()' 
                  FROM #Products t2
                  WHERE t2.CodeId = t1.CodeId
                  FOR XML PATH('')), 1, 1, '') AS [Keywords], t1.[CodeId], t1.[Manufacturer]
   FROM #Products t1
   GROUP BY t1.[Category], t1.[CodeId], t1.[CodeId], t1.[Manufacturer]

DROP TABLE #Products

Result:

Category	Keywords	CodeId	Manufacturer
xx	C201,C201,C213,C213,C606,C606	02300877	Samsung
xx	C201,C201,C213,C213,C606,C606	02300877	Toshiba
xx	C303,C305,C712	02301163	Samsung
xx	C303,C305,C712	02301163	Toshiba
xx	C207,C209,C708	02301165	Philips
xx	C207,C209,C708	02301165	Samsung

I hope it will be helpful...

[/EDIT]