Page 1 of 1
single row spliting into Multiple rows in one column
Posted: Fri Nov 27, 2009 6:04 am
by vinsashi
Hi ray,
my input like this
Code: Select all
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...
Posted: Fri Nov 27, 2009 9:02 am
by chulett
"Hi Ray"? Seriously?
That's a
horizontal pivot aka columns to rows and oddly enough there's a stage for that.
Posted: Mon Nov 30, 2009 8:35 am
by vinsashi
Hi,
Please help me for this...
Thanks
vin...
Posted: Mon Nov 30, 2009 8:42 am
by datskosaraju
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?
Posted: Mon Nov 30, 2009 8:44 am
by datskosaraju
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?
Posted: Mon Nov 30, 2009 8:51 am
by vinsashi
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..
Posted: Mon Nov 30, 2009 8:57 am
by chulett
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".
Posted: Mon Nov 30, 2009 9:00 am
by chulett
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.
Posted: Mon Nov 30, 2009 11:25 pm
by vinsashi
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
...
Posted: Mon Nov 30, 2009 11:31 pm
by ray.wurlod
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.
Posted: Mon Nov 30, 2009 11:47 pm
by chulett
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.
Posted: Tue Dec 01, 2009 12:13 am
by vinsashi
Hi,
Thanks a lot.
thanks
vin..