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

One Star

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

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
Seven Stars

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

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.
One Star

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

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
One Star

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

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
One Star

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

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
One Star

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

 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
                         
Moderator

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

Hi manjunath.m,
Here is a component TalendHelpCenter:tNormalize which normalizes the input flow following SQL standard.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.