Page 1 of 1
Split a column into multiple rows
Posted: Fri Jul 23, 2010 9:17 pm
by datastagenewbie
Hi!
I have two columns in a table
ColA ColB
43459 30495
43463 28000300010800110001
I need the output to be
ColA ColB
43459 30495
43463 28000
43463 30001
43463 08001
43463 10001
In other words i have to split colA into multiple rows of size 5
Any help is appreciated
Thanks in advance.
Posted: Fri Jul 23, 2010 9:57 pm
by anbu
In transformer have 5 outputs and derivations for each output is
Code: Select all
ColA,ColB[1,5]
ColA,ColB[6,5]
ColA,ColB[11,5]
ColA,ColB[16,5]
ColA,ColB[21,5]
Posted: Sat Jul 24, 2010 3:35 am
by ray.wurlod
... and bring them back together with a Funnel stage.
Posted: Sat Jul 24, 2010 7:17 am
by datastagenewbie
Yes, we could do that if ColB wasn't of lenght 800
Posted: Sat Jul 24, 2010 7:37 am
by chulett
Why not mention that originally? So...
up to 160 records per?
Posted: Sat Jul 24, 2010 8:21 am
by datastagenewbie
Sorry, I should have mentioned that. My bad!
Posted: Sat Jul 24, 2010 8:37 am
by anbu
In transformer define 160 columns and assign ColB[1,5],ColB[6,5],ColB[11,5] ,ColB[16,5] ,ColB[21,5]... to it
Then use Pivot stage
Posted: Sat Jul 24, 2010 9:06 am
by chulett
... and then constrain out the nulls after that.
Posted: Mon Jul 26, 2010 1:52 am
by kumar_s
Or Optionally the Column Import can be used.