Vertical pivot
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
Vertical pivot
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
Thanks in Advance
hi sam here
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
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.
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.
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
Input id ---
Output should be --
[/list]
Code: Select all
Key_col Code_col
123 100
123 200
123 300
345 500
345 600
345 700
345 800
Code: Select all
Key_col Code_col1 Code_col2 Code_col3 Code_col4
123 100 200 300
345 500 600 700 800
hi sam here
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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 wrote: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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.samsuf2002 wrote: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...
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'.ag_ram wrote: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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
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.
hi sam here