Vertical pivot

[samsuf2002]

Hi All , I have to do vertical pivot in server , my requirement is i have 2 cols A $ B in source table and i want to get one row in output having same A value and different B values in multiple columns. Can any one please show me how to handle this requirement.

Thanks in Advance

[chulett]

First question would be - have you searched this forum for 'vertical pivot'? The technique has been explained quite a number of times.

[nivas]

This topic earlier explained. But I would like to give brief info.

You need to use variables and concatenate the cols based on key col . You need to use hash file and make the key col as the key in hash file. There by you will have the vertical pivot records in hash file.

[samsuf2002]

Nivas- I did as you said and as i found in some of the helpful posts but now my output looks like key col A and B col having values X,Y,Z,W ....but my requirement is to get X,Y,Z,W in 4 different columns...

[chulett]

Post examples - sample input records and what they should look like after the pivot.

[ag_ram]

Upto my understanding, the Requirement is to create output columns which need to be as many different values as in the particular Column of the Input records.

If the assumed requirement is correct, Solution is most probably impossible, since the creation of Input/Output medadata should not be depending on the data flowing in.

[samsuf2002]

Input id ---

Code: Select all

Key_col Code_col
123       100
123       200
123       300
345       500
345       600
345       700
345       800

Output should be --

Code: Select all

Key_col Code_col1 Code_col2 Code_col3 Code_col4
123        100       200       300      
345        500       600       700       800 

[/list]

[chulett]

Maximum number of pivoted columns is known? Always 4?

[samsuf2002]

No Chulett maximum number of pivoted columns can be more upto 20.

[ray.wurlod]

Upto my understanding, the Requirement is to create output columns which need to be as many different values as in the particular Column of the Input records.

If the assumed requirement is correct, Solution is most probably impossible, since the creation of Input/Output medadata should not be depending on the data flowing in.

Nothing is impossible. A simple solution that handles an arbitrary number of input rows (output columns) can be readily accomplished using the multi-valued metadata handling capability of the UV stage.

[ag_ram]

Of course. I believe that If the file is a Hashed file or ready-to-be-converted-as-Hashed file, We could take advantage of Hashed File concept and derive the solution using multi-valued metadata handling capacity of the UV stage as ray.wurlod reacted.

[Rajee]

Nivas- I did as you said and as i found in some of the helpful posts but now my output looks like key col A and B col having values X,Y,Z,W ....but my requirement is to get X,Y,Z,W in 4 different columns...

I have also implemented vertical pivoting much similar to how Nivas has explained.But instead of writing it to hash file I used stage variables and stored the values of all the columns(4 pivot columns) seperated by a delimiter in one stage variable and then I used the Field function to extract each column value seperately and wrote it to a sequential file.I think my approach may help you out in implementing your logic.

[chulett]

Of course. I believe that If the file is a Hashed file or ready-to-be-converted-as-Hashed file, We could take advantage of Hashed File concept and derive the solution using multi-valued metadata handling capacity of the UV stage as ray.wurlod reacted.

Or for us Luddites who didn't cut our teeth on multi-valued metadata, a plain old hashed file can be used as long as the maximum number of columns to pivot is known - even if it is 'up to 20'.

Read from and write to the same hashed file. Each write to the hashed file populates one more of the of the data fields in order. You don't even need sorted input for this technique, unless your 'code_col' needs to be pivoted in order.

[samsuf2002]

Thanks to all, its working fine....i used the idea given by Rajee by holding code values in one stage variable and then using Field() to split the values in different output columns (in my case it will be 20 output columns)...... Now my concern is whether its worth using hashed file because my data can be around 9 million.

[chulett]

If you've got it working fine now... I certainly wouldn't worry about changing it. Just keep that in mind as a possibility next time.