Pivot Stage - Columns to Rows

anupam · Post by **anupam** » Wed Feb 04, 2004 1:07 am

Hi all,

I have input file which have

Col1,col2,col3,col4
1,2,3,4
5,6,7,8
9,10,11,12
13,14,15,16

I want the output

col1,col2,col3,col4
1,5,9,13
2,6,10,14
3,7,11,15
4,8,12,16

I am using pivot stage but could not get the desired output.

Actually i am using this stage for the 1st time and don't have any idea whar derivation i should write to achieve this output.

Please suggest

ray.wurlod · Post by **ray.wurlod** » Wed Feb 04, 2004 1:18 am

There is a separate manual for each plug-in stage type. These are in the Packages folder on the DataStage clients CD.
When I install plug-in stages I copy the manual to the DataStage Docs folder on the client, or to a shared driver, so that they remain readily accessible without having to dig out the CD.
The examples in the Pivot stage manual (pivot.pdf) are quite clear; you can learn to drive it in about 10 minutes.

All that said, what you're attempting to do is not a pivot, it's a transpose.

There is no dedicated stage type for performing a transpose. It's not a common thing to want to do at all in ETL. Would you like to tell us just what it is you're trying to accomplish? I could easily solve the four rows, four columns problem that you posed, but I don't think that's actually the problem that you want solved.

anupam · Post by **anupam** » Wed Feb 04, 2004 1:27 am

Hello Ray,

Right u r Mr.Ray. It's not the problem i am trying to solve. Actually i have not tried pivot stage, so i was just doing R&D. Actually i saw the manual also but the possible derivations are not given there.

So i thought may be i am missing something. Ray can i get the possible derivations applicable to pivot stage anywhere. I tried substring, <> (not equal) but they were not working in pivot stage.

What are the derivations possible in pivot stage.

ray.wurlod · Post by **ray.wurlod** » Wed Feb 04, 2004 1:34 am

The Pivot stage does NOT change data. You use a Transformer stage to change data.

The only derivations in the Pivot stage are:
(a) empty (a key column from the input is a pivot key - preserved in every output row)
(b) a single input column (a non-key column from the input is a pivot key - preserved in every output row)
(c) a comma-delimited list of input columns (the input columns for each of which an output row will be generated)

See pages 4-7 of the manual.

anupam · Post by **anupam** » Wed Feb 04, 2004 1:38 am

Hello Ray,

Right u r Mr.Ray. It's not the problem i am trying to solve. Actually i have not tried pivot stage, so i was just doing R&D. Actually i saw the manual also but the possible derivations are not given there.

So i thought may be i am missing something. Ray can i get the possible derivations applicable to pivot stage anywhere. I tried substring, <> (not equal) but they were not working in pivot stage.

What are the derivations possible in pivot stage.

ray.wurlod · Post by **ray.wurlod** » Wed Feb 04, 2004 1:51 am

What's not clear in my previous post?

You can NOT use expressions in the Derivations column on the Outputs tab of the Pivot stage. This grid is ONLY for specifying how the input columns are to be pivotted onto the output link.

anupam · Post by **anupam** » Wed Feb 04, 2004 2:15 am

Coming back to my question, How to acheive the desired output then which is mentioned in my first mail

ray.wurlod · Post by **ray.wurlod** » Wed Feb 04, 2004 2:51 pm

Do you want the solution to the four rows, four columns transpose (which is what you sought in your first mail)?
Probably the easiest is to use a Routine or stage variables to capture and accumulate the results, write the output to a text file, then subsequently process just the last four lines of the text file (tail -4 filename).

You still haven't revealed why you want to transpose data. It's highly unusual to need to do so as part of ETL.

Jay · Post by **Jay** » Thu Feb 26, 2004 8:27 pm

hi ray,

i have to do the exact same operation...transpose all rows to columns...
the data comes from a mainframe....well thats how it is sometimes...i think its 30-40 row-col transpose...

one way is to write the existing SQL code that is being used. this shud b slow.
the other u suggested shud b faster.

but i am working on PX. so lets c what is the performance...

i will let u know about the results. but when, i have no clue...

thanks
jay

vmcburney · Post by **vmcburney** » Fri Feb 27, 2004 2:20 am

I think with 30 or 40 columns I would write a generic routine that reads a sequential file, transposes the data based on one or more key fields and writes it out again. Much easier than trying to transpose it in a transformer with stage variables and it should be faster too.