How do I find same values in a specific column of a datatable and merge their other colmns ?

Question

4.00/5 (1 vote)

See more:

for example my data table is like this:
dt1:
code| Number
-----------
A | 12

B | 1

A | 4

A | 2

B | 5

Assume that datatable 2 has 4 column.I need a datatable or even list like this:
dt2:
code| Number1|Number2 | Number3
---------------------------------
A | 12 | 4 | 2

B | 1 | 5 |

my uncompleted code:

C#

DataTable dt1 = ds.Tables[0];                        
for(int i=0;i< dt1.Rows.Count;i++)
{
  string code=dt1.Rows[i].ItemArray[0].ToString();
  bool existence=false;
  for(int j=0;j< dt2.Rows.Count;j++)
  {
    if(dt2.Rows[j]["code"]==code)
     {
       existence=true;
       //add dt1[i]["number"] to dt2[j][next columns which is not filling]????
       break;
     }
  }
  if(!existence)//not existence
  {
    //dt2.add(dt1.row[i])
  }
}

I don't know how to code the first comment I determine above
and if there is another simple way to solve this question

Posted 24-Sep-14 21:08pm

mit62

Updated 25-Sep-14 3:06am

v3

Add a Solution

3 solutions

Solution 1

What you need is to PIVOT your columns, try this article Pivot two or more columns in SQL Server 2005[^]

Posted 24-Sep-14 21:30pm

Mycroft Holmes

Comments

mit62 25-Sep-14 9:01am

thanks a lot.I didn't know about pivot tables. But I need a solution in c# and I found it.

Solution 2

Try this

http://dba.stackexchange.com/questions/30119/pivot-rows-into-multiple-columns[^]

Posted 24-Sep-14 23:52pm

Santhosh Babu Mahimairaj

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)

mit62 · Accepted Answer · 2014-09-25T02:59:00

my solution is this:

C#

 DataTable dttmp = ds.Tables[0];
var dic = new Dictionary<String,List<String>>();
for (int i = 0; i < dttmp.Rows.Count; i++)
{
    string code = dttmp.Rows[i].ItemArray[0].ToString();
    string time = dttmp.Rows[i].ItemArray[1].ToString();
    if (code != "0")
    {
        if (!dic.ContainsKey(code))
            dic.Add(code, new List<string>(){time});
        else
            dic[code].Add(time);
        
    }
}
</string>