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.
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
Highlighted
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