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
6 REPLIES
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.