如何合并数据表中的相似行(行仅相差一列)? [英] How to combine similar rows in a Datatable(the rows only differ by one column)?

问题描述

我有三个要处理的子表.对于每一个我想要合并的行，因为它们仅在第二列中的内容上有所不同(我稍后将对第四列进行相同的操作):

''子表1

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

''子表2

''Sub table 2

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

''子表3

'' 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 />

现在，第二行将保存出现在子表中的所有不同的"Cxxx"信息.我对vb相当陌生，我很欣赏一些示例代码，这些示例代码可以完成数据表中的组合行"部分，甚至可能完成最后的聚合部分.任何建议的赞赏.谢谢大家.

p.s.对于第四列制造商信息"，我想做同样的事情，我可能会在决赛桌中得到以下内容:

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

已添加代码标签-LOSMAC [/EDIT]

Code tags added - LOSMAC[/EDIT]

推荐答案

在这里看看: 3)从剪贴板粘贴代码
4)按执行"按钮

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.


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

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

结果:

类别	关键字	CodeId	制造商
xx	C201，C201，C213，C213，C606，C606	02300877	三星
xx	C201，C201，C213，C213，C606，C606	02300877	东芝
xx	C303，C305，C712	02301163	三星
xx	C303，C305，C712	02301163	Toshiba
xx	C207， C209，C708	02301165	Philips
xx	C207，C209，C708	02301165	三星

希望对您有所帮助...

[/EDIT]

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]

嗨 使用sql命令从每个表中选择数据
然后使用数据读取器从表中读取数据并组合field2数据(或先使用move，然后在循环中通过组合每个记录的field2数据使用movenext satatation)，然后将它们插入最终表中
与其他表相似
对不起，但我不知道您使用的是哪种数据库
祝你好运

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

这篇关于如何合并数据表中的相似行(行仅相差一列)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！