topic Re: Split one row into multiple rows, keeping no. of columns same in Talend Studio

Split one row into multiple rows, keeping no. of columns same

Anonymous — Sat, 16 Nov 2024 12:54:34 GMT

Hi,
I have a MySQL table with numeral values in a column (that I expect not to increase beyond a number, 15 in particular case). If this values exceeds a predetermined number, I want it to split it into smaller values (each value being less than 15, based upon a criteria that I can not put up here), and distribute into different rows.
For example, if the rows reads something like
id | name | value
1 | abc | 33
2 | pqr | 12
expected output:
id | name | value
1 | abc | 11
2 | abc | 14
3 | abc | 8
4 | pqr | 12
I read that tMap does something similar this (basically for fixed number of output rows), but don't know if it gives this for dynamic number of rows?
thanks
Gn

Re: Split one row into multiple rows, keeping no. of columns same

alevy — Tue, 24 May 2011 23:29:13 GMT

Your best approach might be to use tMap or tJavaRow to generate a delimited string of your new values e.g. "11,14,8" and then use tNormalize to split it into multiple rows. You'll have to add the id afterwards.

Re: Split one row into multiple rows, keeping no. of columns same

_AnonymousUser — Mon, 06 Aug 2012 08:15:46 GMT

Hello Gnyani,
You can use the combination of tNormalize & or tExtractDlimitedFields to resolve your problem.
Provided If you can write code that splits your data column to split in comma-seperated values.
Regards,
Nirav Shah

Re: Split one row into multiple rows, keeping no. of columns same

_AnonymousUser — Wed, 05 Dec 2012 07:21:25 GMT

hi,
i have a similar problem. Please find the below details and advice the best process.

Source Table:
SELECT * FROM SRC_1;
NRI_NUM SA MA OA SAB MAB OAB
----------- ----- ----- ----- ---- --- ---
1 SA123 MA456 OA789 10 20 30
Target Table Details:
SELECT * FROM TRG_1;
NRI_NUM ACT_NUM ACT_TYPE ACT_BAL
----------- ---------- ---------- ----------
1 123 SA 10
2 456 MA 20
3 789 OA 30

Re: Split one row into multiple rows, keeping no. of columns same

_AnonymousUser — Wed, 05 Dec 2012 09:14:44 GMT

Hi,
I was able to achive it using tSplitRow component.
NRI_NUM ACT_NUM ACT_TYPE ACT_BAL
----------- ---------- ---------- ----------
1 SA 123 10
1 MA 456 20
1 OA 789 30

Re: Split one row into multiple rows, keeping no. of columns same

Anonymous — Wed, 04 Nov 2015 09:56:12 GMT

hi, I have source like
ID,NAME
1&2,A&B
3/4,C/D
5&6,E/F
In my target the data would be like
ID,NAME
1,A
2,B
3,C
4,D
5,E
6,F
How can i achieve this can any one help me

Re: Split one row into multiple rows, keeping no. of columns same

Anonymous — Wed, 04 Nov 2015 11:08:52 GMT

Hi manjunath.m,
Here is a component TalendHelpCenter:tNormalize which normalizes the input flow following SQL standard.
Best regards
Sabrina