How to merge multiple rows?

One Star

How to merge multiple rows?

I have a question about merge multiple rows from DB query. For example, query results:
id col1 col2 col3 col4
row1 key1 key2 AB 5
row2 key1 key2 ABC 8
Merge condition is:
row1.col1= row2.col1
and row1.col2=row2.col2
and row1.col3 like 'AB%'
and row2.col3 like 'AB%'
Then let row2.col4 = row2.col4+row1.col4. So after merge, output should be:
id col1 col2 col3 col4
row2 key1 key2 ABC 13
How can i make it? Appreciate your help!
One Star

Re: How to merge multiple rows?

Hi
I need to make sure one thing. All these rows are from one table or two table?
If they are in the same table, how many rows in it?
Regards,
Pedro
One Star

Re: How to merge multiple rows?

Hi
I need to make sure one thing. All these rows are from one table or two table?
If they are in the same table, how many rows in it?
Regards,
Pedro

Hi, Pedro
They are from one store procedure result.
One Star

Re: How to merge multiple rows?

Hi
What about three columns?
row1 key1 key2 AB 5
row2 key1 key2 ABC 8
row3 key1 key3 ABC 3
What's the rule for row3?
Regards,
Pedro
One Star

Re: How to merge multiple rows?

Hi
What about three columns?
row1 key1 key2 AB 5
row2 key1 key2 ABC 8
row3 key1 key3 ABC 3
What's the rule for row3?
Regards,
Pedro

Just output row3 directly, because it couldn't be merged on either row1 or row2. Merge conditon is
row1.col1= row2.col1
and row1.col2=row2.col2
and row1.col3 like 'AB%'
and row2.col3 like 'AB%'
if row3.col2 equals key2 not key3, it will be merged! Thanks for your quick reponse.
One Star

Re: How to merge multiple rows?

Hi
You might create a job as the following images.
Code in tJava
//Code generated according to input schema and output schema
Pattern pt = Pattern.compile("^AB*.");
String sm1 = col3_tMemorizeRows_1==null?"":col3_tMemorizeRows_1;
String sm0 = col3_tMemorizeRows_1;
String col1_1= col1_tMemorizeRows_1==null?"":col1_tMemorizeRows_1;
String col2_1= col2_tMemorizeRows_1==null?"":col2_tMemorizeRows_1;
Matcher m1=pt.matcher(sm1);
Matcher m0=pt.matcher(sm0);
if((col1_1.equals(col1_tMemorizeRows_1))&&
(col2_1.equals(col2_tMemorizeRows_1))&&m1.find()&&m0.find()){
output_row.id = id_tMemorizeRows_1;
output_row.col1 = col1_tMemorizeRows_1;
output_row.col2 = col2_tMemorizeRows_1;
output_row.col3 = col3_tMemorizeRows_1;
output_row.col4 = col4_tMemorizeRows_1+col4_tMemorizeRows_1;
}else{
output_row.id = input_row.id;
output_row.col1 = input_row.col1;
output_row.col2 = input_row.col2;
output_row.col3 = input_row.col3;
output_row.col4 = input_row.col4;
}

Regards,
Pedro
One Star

Re: How to merge multiple rows?

Hi
You might create a job as the following images.
Code in tJava
//Code generated according to input schema and output schema
Pattern pt = Pattern.compile("^AB*.");
String sm1 = col3_tMemorizeRows_1==null?"":col3_tMemorizeRows_1;
String sm0 = col3_tMemorizeRows_1;
Matcher m1=pt.matcher(sm1);
Matcher m0=pt.matcher(sm0);
if((col1_tMemorizeRows_1==col1_tMemorizeRows_1)&&
(col2_tMemorizeRows_1==col2_tMemorizeRows_1)&&m1.find()&&m0.find()){
output_row.id = id_tMemorizeRows_1;
output_row.col1 = col1_tMemorizeRows_1;
output_row.col2 = col2_tMemorizeRows_1;
output_row.col3 = col3_tMemorizeRows_1;
output_row.col4 = col4_tMemorizeRows_1+col4_tMemorizeRows_1;
}else{
output_row.id = input_row.id;
output_row.col1 = input_row.col1;
output_row.col2 = input_row.col2;
output_row.col3 = input_row.col3;
output_row.col4 = input_row.col4;
}

Regards,
Pedro

Hi Pedro,
i am still confused how did you generate the code? where is your merge conditon definition? Thanks a lot.
Highlighted
One Star

Re: How to merge multiple rows?

Hi
The merge condition definition is decided by 'if' statement.
if((col1_tMemorizeRows_1==col1_tMemorizeRows_1)&&
(col2_tMemorizeRows_1==col2_tMemorizeRows_1)&&m1.find()&&m0.find())
In fact, your requirement is complicated. It took me time to write these code.
Regards,
Pedro
One Star

Re: How to merge multiple rows?

Hi
The merge condition definition is decided by 'if' statement.
if((col1_tMemorizeRows_1==col1_tMemorizeRows_1)&&
(col2_tMemorizeRows_1==col2_tMemorizeRows_1)&&m1.find()&&m0.find())
In fact, your requirement is complicated. It took me time to write these code.
Regards,
Pedro

Thanks again, Pedro. But looks like records are not merged.
input:
row1;key1;key2;AB;5
row2;key1;key2;ABC;8
row3;key1;key3;ABC;3
output should be:
row2;key1;key2;ABC;13
row3;key1;key3;ABC;3
One Star

Re: How to merge multiple rows?

Hi
I have corrected my mistake.
You will see the right solution at Comment #6 this time.
Besides, you have to remove the first line manually.
Regards,
Pedro
One Star

Re: How to merge multiple rows?

Hi,
I need solution on the below query.
i have two tables table A, table B
Table A has one column task_id
Table B has two cols task_id,task_status
task_id                                status
-----------                    -----------------
10                                in progress
10                                completed 
I need table c to have 
task_id                    history_status
----------               ---------------
10                            in progress,completed 
thanks
Devashsi
Four Stars

Re: How to merge multiple rows?

Hi,
This could be achieved using denormalization... pl check following scenario, you will get an idea.
https://help.talend.com/search/all?query=tDenormalize&content-lang=en
Vaibhav

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog