How do I merge data in same datatable

Question

1.00/5 (2 votes)

See more:

Data in DataTable as

Time 	Col1	Col2
10:00	1	
11:00	2	
10:00		3
11:00		4

I need to arrange it as
Time Col1 Col2
10:00 1 3
11:00 2 4

What I have tried:

I currently used 2 different table for merging, which is consuming time for huge data.

Posted 16-Jan-23 2:58am

Member 12694392

Updated 16-Jan-23 17:11pm

Add a Solution

Comments

Graeme_Grant 16-Jan-23 10:17am

How are you displaying the DataTable? Are you using a DataGridView? Please update your question with some code, by using the "Improve question" link, so that we can see what you are doing.

Member 12694392 16-Jan-23 10:19am

No, will be passing it to RDLC report.

3 solutions

Solution 2

I have never used RDLC. I am going to assume that RDLC DataSource binding works the same as other WinForm controls, like the DataGridView.

My thoughts are:
* use a single DataTable, not 2.
* Add a filter column to the DataTable - FilterColumn for the purposes of this solution

Now we can do the following:

C#

dataTable.Columns.Add(new DataColumn("FilterColumn"));

DataView view1 = dataTable1;

BindingSource bindingSource1 = new();
bindingSource1.DataSource = view1;
bindingSource1.Filter = "FilterColumn = col1"

rdlc1.DataSource = bindingSource1;

BindingSource bindingSource2 = new();
bindingSource2.DataSource = view1;
bindingSource2.Filter = "FilterColumn = col2"

rdlc2.DataSource = bindingSource2;

(note: this is hand-coded and not tested)

In concept, you have two different reports using a single set of data. No need to manually merge.

Posted 16-Jan-23 4:36am

Graeme_Grant

Comments

Member 12694392 16-Jan-23 10:46am

I am using single DataTable only, the challenge is only with data not merged. hence timestamp get duplicated everytime

Graeme_Grant 16-Jan-23 10:48am

Where is this other data coming from? Do you only have one RDLC or two or more?

This is where posting your code helps. Otherwise, we are left guessing.

Please post some code.

UPDATE: What I proposed is not having 2 columns, use one and a filter. No need to do any merging as already merged.

Member 12694392 16-Jan-23 10:52am

I have created function in PostgreSQL which returns me data as below, function is called in loop w.r.t.number of columns i.e Value from user input.

string cmd = @"select time_stamp,value from data(arguments)
var oda = new NpgsqlDataAdapter(cmd, DBConnection.Pconn);
oda.Fill(datatable);
datatable.Columns["value"].ColumnName = StringUniqueColumnNamefilledatRunTime;

The Above result is as below

Time 	Col1	Col2
10:00	1	
11:00	2	
10:00		3
11:00		4

required output is

Time Col1 Col2
10:00 1 3
11:00 2 4

Graeme_Grant 16-Jan-23 10:54am

What I proposed was:

Time    Data	FilterCol
10:00     1	     Col1
11:00     2	     Col1	
10:00     3	     Col2
11:00     4	     Col2

Member 12694392 16-Jan-23 10:56am

This is duplicating time column, which is not not required.

Graeme_Grant 16-Jan-23 10:57am

Your question was: "How do I merge data in same datatable?" Your question gives no context and has no code.

The filters are what decide what is viewed, so the data is already merged.

Graeme_Grant 16-Jan-23 11:02am

Here is an example from another question [asked yesterday] that allows you to see the filter in action: Filter datagrideview by bindingsource[^]

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)

Member 12694392 · Accepted Answer · 2023-01-16T17:11:00

Maciej Los your solution works for specific column count and data gets stored in List.
I achieved the same with below logic and in datatable only.

DT1.Columns["value"].ColumnName = TagNamePlain;
DT1.PrimaryKey= new DataColumn[] { DT1.Columns[0] };
DT2.Merge(DT1);
DT2.AcceptChanges();

I achieve the required output by assigning primary key with merge.
Thanks

Maciej Los · Accepted Answer · 2023-01-16T09:13:00

Note: your data are stored in wrong way...

I'd suggest to join data in col1 with col2 based on time column. Take a look at below code:

DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[]
	{
		new DataColumn("Time", typeof(string)),
		new DataColumn("Col1", typeof(int)),
		new DataColumn("Col2", typeof(int))
	});
	
dt.Rows.Add(new object[]{"10:00", 1, null});
dt.Rows.Add(new object[]{"11:00", 2, null});
dt.Rows.Add(new object[]{"10:00", null, 3});
dt.Rows.Add(new object[]{"11:00", null, 4});


var c1 = dt.AsEnumerable()
	.Where(x => !DBNull.Value.Equals(x["Col1"]))
	.Select(x=> new {Time = x.Field<string>("Time"), Col1 = x.Field<int>("Col1")})
	.ToList();
var c2 = dt.AsEnumerable()
	.Where(x => !DBNull.Value.Equals(x["Col2"]))
	.Select(x=> new {Time = x.Field<string>("Time"), Col2 = x.Field<int>("Col2")})
	.ToList();
	
var result = (from cc1 in c1
			join cc2 in c2 on cc1.Time equals cc2.Time
			select new { cc1.Time, cc1.Col1, cc2.Col2})
			.ToList();

Result:

Time  Col1 Col2
10:00 1    3 
11:00 2    4

If you would like to get result as a datatable object, use CopyToDataTable method.
See: DataTableExtensions.CopyToDataTable Method (System.Data) | Microsoft Learn[^]

How do I merge data in same datatable

3 solutions

Solution 4

Solution 2

Solution 3

Add your solution here

Preview 0