I have a situation where a field in the data row in the source is a string delimited by a ','.
eg:
COL1 COL2
1 1, 2, 3, 4, 5
2 A, B, C, D
3 g, h, i
Here is what I am trying to achieve:
count the number of occurences ',' and create an individual row.
eg:
COL1 COL2
1 1
1 2
1 3
1 4
1 5
2 A
2 B
2 C
2 D
3 g
3 h
3 i
Can the Pivot stage supports this type of parsing?
Any advise on acheiving this is appreciated.
Parse question
Moderators: chulett, rschirm, roy
Thanks Chulett. So it can be done using pivot if I set the threshold(max to a number. Say, 20). For my eralier sample data set, how do I define the derievation in the Pivot stage.chulett wrote:Pivot pivots, you need to parse. If you know the max # of fields, parse out that single field to the max, pivot and then filter out any nulls. No counting required. ...
Any help is appreciated!
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: