Comma delimited multi list to rows

[namerup]

How to concatenate multiple comma-separated lists, as in the following example.

-> INPUT

ID        |       ColA     |     ColB      |     ColC       |

--------------------------------------------------------------

1          | 1A              | 1B              | 1C              |

2,3,4    | 2A, 3A, 4A | 2B, 3B, 4B | 2C, 3C, 4C |

5,6       | 5A, 6A       | 5B, 6B        | 5C, 6C       |

<- OUTPUT

1          | 1A              | 1B              | 1C              |

2          | 2A              | 2B              | 2C              | 

3          | 3A              | 3B              | 3C              | 

4          | 4A              | 4B              | 4C              | 

5          | 5A              | 5B              | 5C              | 

6          | 6A              | 6B              | 6C              | 

Any ideas or suggestions?
Thanks.

[tresesco]

Check Subfield()

[jeoste]

Hello
Using Talend ETL have a look to tNormalize component
https://www.tutorialgateway.org/talend-tnormalize/

[namerup]

"TNormalise" works for a single comma separated column. But in a multiple comma separated column?

[TauseefKhan · Accepted solution]

Hi @namerup 
Check with this:

// Create a temporary table to hold the maximum number of comma-separated values
TempTable:
LOAD
Max(Len(ID) - Len(Replace(ID, ',', ''))) as MaxCommas
RESIDENT InputTable;

LET vMaxCommas = Peek('MaxCommas', 0, 'TempTable') + 1;

DROP TABLE TempTable;

// Create the output table by splitting the comma-separated values
OutputTable:
LOAD
SubField(ID, ',', IterNo()) as ID,
SubField(ColA, ',', IterNo()) as ColA,
SubField(ColB, ',', IterNo()) as ColB,
SubField(ColC, ',', IterNo()) as ColC
RESIDENT InputTable
WHILE IterNo() <= vMaxCommas;

[namerup]

Thanks.