convert single row data into multiple rows

[marchana]

hi,

I need a solution for one of my scenario. it describes below.

My input columns are id,data and its value are

id , data
1, ad|fh|ju

i have to split this data as like below

id, data
1,ad
1,fh
1,ju

I am using datastage parallel , so kindly give me the solution in respect of that.


Thanks in advance for your reply.I am waiting for the solution.

[Devendrudu]

Hi marchana,
u can do this by using substring function in transformer
substring(colname,[1|1])=col1
substring(colname,[4|1])=col2
substring(colname,[7|1])=col3

after that u can take one pivot stage in o/p give col derivation col1,col2,col3

[ray.wurlod]

It is probably easier to use the Field() function in a Transformer stage to isolate the elements from the delimited list, or you could use a Column Import stage to parse them.

That having been done, use a Pivot stage to convert the rows to columns.

Or, if you're using a Transformer stage anyway, you could use that to effect the pivot as well.

[marchana]

hi, there is a good news. i solved that problem.

thanks for all who replied for me. ray , i couldn't able to see the full content as i am not a member of that.


Here is the way i did,

From the database itself, i took one more column which contains the number generated by row_number () function limited to the length of the column data.

id, data, no
1, ad|fh|ju, 1
1, ad|fh|ju, 2
1, ad|fh|ju, 3...........

then i took count of the data column with respect to the delimiter and i put condition in the transformer.Finally i used field function to get the data separately....

[fmou]

Hi,

It's already pretty clear how to isolate the fields, but my question is how to write multiple rows into target from a single row input?

Use transformer for multiple outputs then funnel them together? Any better ideas?

Thanks

[chulett]

As noted, a pivot.

[PhilHibbs]

It's already pretty clear how to isolate the fields, but my question is how to write multiple rows into target from a single row input?
Use transformer for multiple outputs then funnel them together? Any better ideas?

Either a pivot, as already suggested, or if you're using 8.5 then a Loop within a Transformer is another way. Pivot would be best if there is a fixed maximum number of entries, a loop would be better if there is no clear upper limit (although the length of the char field would give a de facto limit, but I wouldn't be entirely comfortable converting this into a hard limit).