Split a column into multiple rows

datastagenewbie · Post by **datastagenewbie** » Fri Jul 23, 2010 9:17 pm

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.

anbu · Post by **anbu** » Fri Jul 23, 2010 9:57 pm

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]

ray.wurlod · Post by **ray.wurlod** » Sat Jul 24, 2010 3:35 am

... and bring them back together with a Funnel stage.

datastagenewbie · Post by **datastagenewbie** » Sat Jul 24, 2010 7:17 am

Yes, we could do that if ColB wasn't of lenght 800

chulett · Post by **chulett** » Sat Jul 24, 2010 7:37 am

Why not mention that originally? So... up to 160 records per?

datastagenewbie · Post by **datastagenewbie** » Sat Jul 24, 2010 8:21 am

Sorry, I should have mentioned that. My bad!

anbu · Post by **anbu** » Sat Jul 24, 2010 8:37 am

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

chulett · Post by **chulett** » Sat Jul 24, 2010 9:06 am

... and then constrain out the nulls after that.

kumar_s · Post by **kumar_s** » Mon Jul 26, 2010 1:52 am

Or Optionally the Column Import can be used.