Generating Multiple Rows from single row

[baglasumit21]

My source data is like

Col1, Col2, Col3, SQRNBR
A, B, C, 1|2|5|7|2
X, Y, Z, 1|2|3|7|4|9|1

I want the data in form

A,B,C,1
A,B,C,2
A,B,C,5
A,B,C,7
X,Y,Z,1
X,Y,Z,2
X,Y,Z,3
X,Y,Z,7
X,Y,Z,4
X,Y,Z,9

I need to generate the rows on the basis of seqnbr column where my values are seperated by | and the number of values that are pipe seperated will be the number of rows generated. Please help me to achieve either by Row Splitter or PIVOT or any other solution

[loveojha2]

Use Pivot stage, It would give you what you want with the duplicates, do a search on the handling duplicates, I am sure you will find lots of good posts on handling duplicates.

[baglasumit21]

Use Pivot stage, It would give you what you want with the duplicates, do a search on the handling duplicates, I am sure you will find lots of good posts on handling duplicates.

i used pivot stage but my problem is values which are in col3 concatenated with pipe i dont the exact no of values ithere may be n no of values so in that case how can i use pivot stage?

[ray.wurlod]

Preprocess the pipe delimited column into separate columns before applying the Pivot stage. This is easily accomplished in a Transformer stage.

[baglasumit21]

Preprocess the pipe delimited column into separate columns before applying the Pivot stage. This is easily accomplished in a Transformer stage. ...

The only problem I have with this is that the column sqrnbr which is a pipe delimited has different number of values which are seperated with pipe. in some rows there are 5 values seperated, in some rows thr are 7, and in some case 10. its not fixed.

[ray.wurlod]

Who cares? Generate the maximum possible number of columns that you are likely to encounter. Say 24.

[DSguru2B]

You can also write a small routine to intake all the columns and depending upon the number of duplicate records to be created, generate the rows and send it back.