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
Generating Multiple Rows from single row
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 132
- Joined: Wed Mar 01, 2006 11:12 pm
- Location: Pune
-
- Participant
- Posts: 132
- Joined: Wed Mar 01, 2006 11:12 pm
- Location: Pune
loveojha2 wrote: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?
SMB
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 132
- Joined: Wed Mar 01, 2006 11:12 pm
- Location: Pune
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 wrote:Preprocess the pipe delimited column into separate columns before applying the Pivot stage. This is easily accomplished in a Transformer stage. ...
SMB
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: