single row spliting into Multiple rows in one column

vinsashi · Post by **vinsashi** » Fri Nov 27, 2009 6:04 am

Hi ray,
my input like this

ename                                              managerid
raj,sasi,chum                                     1000
kumar,vin                                          1001
kotak,vatson,jem,pinky                       1002

and i want output like this

Code: Select all

ename         managerid
raj                     1000
sasi                    1000
chum                  1000
kumar                 1001
vin                      1001
kotak                  1002 
vatson                 1002
jem                     1002
pinky                   1002

Thanks in Adavance
vin...

chulett · Post by **chulett** » Fri Nov 27, 2009 9:02 am

"Hi Ray"? Seriously?

That's a horizontal pivot aka columns to rows and oddly enough there's a stage for that.

vinsashi · Post by **vinsashi** » Mon Nov 30, 2009 8:35 am

Hi,
Please help me for this...
Thanks
vin...

datskosaraju · Post by **datskosaraju** » Mon Nov 30, 2009 8:42 am

vinsashi wrote:Hi,
Please help me for this...
Thanks
vin...

As Craig mentioned, There is stage for doing that, the Pivot stage. Was that not clear?

datskosaraju · Post by **datskosaraju** » Mon Nov 30, 2009 8:44 am

vinsashi wrote:Hi,
Please help me for this...
Thanks
vin...

As Craig mentioned, There is stage for doing that, the Pivot stage. Was that not clear?

vinsashi · Post by **vinsashi** » Mon Nov 30, 2009 8:51 am

Hi,
we cant do by using pivot stage for that scenario. i have tried with pivot stage also.in the pivot stage we can merge data from 2more columns to single column.but here singl column row data we are spliting into under same column multiple rows. if any other solution...please tel me

Thanks
vin..

chulett · Post by **chulett** » Mon Nov 30, 2009 8:57 am

Use the Pivot stage. "Merging data from two or more columns to a single column" is exactly the same as "splitting into under same column multiple rows".

chulett · Post by **chulett** » Mon Nov 30, 2009 9:00 am

You will first need to split up those "comma delimited columns" into individual columns first, but there are stages for that as well. You'll probably also need to constrain out any null values post pivot.

vinsashi · Post by **vinsashi** » Mon Nov 30, 2009 11:25 pm

hi,
I have tried with that one.but in my source under one column data will generated dynamically its not fixed

Code: Select all

ename                    mid
aa,bb,cc                1001
cc,dd                     1002
ee,ff,gg,hh,ii          1003

here we not constant under ename we may get more than 5values also with comma.how can we will divide columns with comma deleimeter.
Thanks
...

ray.wurlod · Post by **ray.wurlod** » Mon Nov 30, 2009 11:31 pm

A Column Import or Transformer stage will do it for you. But you need to have sufficient target columns available to receive the values, and these probably need to be nullable.

chulett · Post by **chulett** » Mon Nov 30, 2009 11:47 pm

In other words, provide for the maximum number in your design, remove any null/empty ones post pivot. The Column Import stage or a Transformer leveraging the Field() function will prep the data for the Pivot.

vinsashi · Post by **vinsashi** » Tue Dec 01, 2009 12:13 am

Hi,
Thanks a lot.

thanks
vin..