A,B,C ---> a b c

vinaymanchinila · Post by **vinaymanchinila** » Thu Jun 16, 2005 4:27 pm

Hi ,
I have acolumn where the value is A,B,C and I want this value to be going into 3 columns

A
B
C
How do I do it, tried to look into Sting functions like Field, SubStr but of no use.
Any help is appreciated .
Thanks,

amsh76 · Post by **amsh76** » Thu Jun 16, 2005 5:07 pm

Look at Field function provided there is a delimiter (, in your example) as you shown in the example.

Col 1 Col 1 2 3
A,B,C A B C

amsh76 · Post by **amsh76** » Thu Jun 16, 2005 5:09 pm

Wait a sec...do you mean three rows ???? and not columns

If thats the case then look at Pivot Stage....

Sorry, if I misinterpreted your question.

vinaymanchinila · Post by **vinaymanchinila** » Thu Jun 16, 2005 5:13 pm

Thanks there but I donot know it may even be A,B,C,D,E int he next column and it is varying. I did look into the Field function as said.

vinaymanchinila · Post by **vinaymanchinila** » Thu Jun 16, 2005 5:14 pm

Pivot Stage , can it be used with server jobs ?

amsh76 · Post by **amsh76** » Thu Jun 16, 2005 5:16 pm

What are you trying to achieve, One column to many columns ??

Or One column to many rows ???

And Pivot is used in Server...

pnchowdary · Post by **pnchowdary** » Thu Jun 16, 2005 5:34 pm

vinaymanchinila wrote:Pivot Stage , can it be used with server jobs ?

Yes, pivot stage can be used with server jobs.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Jun 17, 2005 2:48 am

If you want them to go on multiple columns, do you know the target as the target must be properly defined to hold the max number of columns?

talk2shaanc · Post by **talk2shaanc** » Fri Jun 17, 2005 3:21 am

amsh76 wrote:Wait a sec...do you mean three rows ???? and not columns

If thats the case then look at Pivot Stage....

Sorry, if I misinterpreted your question.

Pivot stage cannot be used for rows to columns conversion, pivot will help you transform the other way i.e., columns to rows.

talk2shaanc · Post by **talk2shaanc** » Fri Jun 17, 2005 3:33 am

Assuming that there would be another KEY column along with the column you have mentioned in your example, I would suggest you to refer the below link/post:

viewtopic.php?p=123634&highlight=#123634

ray.wurlod · Post by **ray.wurlod** » Fri Jun 17, 2005 7:15 am

If you want N comma-delimited values into N separate columns in one row (the OP specification used the word "columns"), the output side of a Transformer stage must have Max(N) columns defined, and the Field function used to separate into separate columns.

This design should also be used if N separate rows each with one column are required. The Transformer stage is still used to separate the data into separate columns, then this output is fed into a Pivot stage. The non-matched columns in the Transformer stage output should be set to NULL.