Pivoting of rows

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
UAUITSBI
Premium Member
Premium Member
Posts: 117
Joined: Thu Aug 13, 2009 3:31 pm
Location: University of Arizona

Pivoting of rows

Post by UAUITSBI »

Hello,

A quick question on pivoting:

I have rows as below from the source:

Code: Select all

SchoolYear   Site     Q1A   Q1B   Q1C 
   1112        4731      5       5       5
   1112        0641      5       5       5
   1112        3881      2       4       4
I am trying to achieve below output:

Code: Select all

SchoolYear   Site     Questions   Responses 
   1112        4731        Q1A             5
   1112        4731        Q1B             5
   1112        4731        Q1C             5  
   1112        0641        Q1A             5
   1112        0641        Q1B             5
   1112        0641        Q1C             5     
   1112        3881        Q1A             2
   1112        3881        Q1B             4
   1112        3881        Q1C             4
I have implemented looping in transformer but I was able to achieve the following and yes I have checked the other posts but couldn't really get the solution.

Code: Select all

SchoolYear   Site     Questions   
   1112        4731        Q1A             
   1112        4731        Q1B             
   1112        4731        Q1C               
   1112        0641        Q1A             
   1112        0641        Q1B             
   1112        0641        Q1C                  
   1112        3881        Q1A             
   1112        3881        Q1B             
   1112        3881        Q1C 
   1112        4731        5             
   1112        4731        5             
   1112        4731        5               
   1112        0641        5             
   1112        0641        5             
   1112        0641        5                 
   1112        3881        2             
   1112        3881        4             
   1112        3881        4            
Any advice is appreciated !!

Thanks !!
Last edited by UAUITSBI on Mon Dec 22, 2014 10:58 am, edited 4 times in total.
UAUITSBI
Premium Member
Premium Member
Posts: 117
Joined: Thu Aug 13, 2009 3:31 pm
Location: University of Arizona

Re: Pivoting of rows

Post by UAUITSBI »

Forgot to mention that I have used @ITERATION variable in the looping and i have 59 columns not just 3 so I have to write

Code: Select all

If @ITERATION = 1 Then "Q1A" Else  
If @ITERATION = 2 Then "Q1B" Else  
If @ITERATION = 3 Then "Q1C" Else etc.. 

The above "If then else" is tedious as I have to repeat for 59 columns and I am not sure if this is right approach, please advise !
UAUITSBI
Premium Member
Premium Member
Posts: 117
Joined: Thu Aug 13, 2009 3:31 pm
Location: University of Arizona

Re: Pivoting of rows

Post by UAUITSBI »

Is this a combination of Vertical pivoting and Horizontal pivoting ?
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

No, this is just a horizontal pivot. I don't see any need off the top of my head for transformer looping and there are other posts here that discuss how to pivot 'column headings' along with their associated data.

Create 59 new columns in the transformer with whatever values you need for each "questions" and pivot them along with their associated columns. I'll see if I can find an example where this is already illustrated.
Last edited by chulett on Tue Dec 23, 2014 4:53 pm, edited 1 time in total.
-craig

"You can never have too many knives" -- Logan Nine Fingers
rschirm
Premium Member
Premium Member
Posts: 27
Joined: Fri Dec 13, 2002 2:53 pm

Post by rschirm »

Just use the enterprise pivot stage to do exactly what you want to do.
UAUITSBI
Premium Member
Premium Member
Posts: 117
Joined: Thu Aug 13, 2009 3:31 pm
Location: University of Arizona

Post by UAUITSBI »

Thanks for the response chulett. I have achieved this in transformer looping with a simple If then else logic using @iteration variable. Earlier I used horizontal pivot through pivot stage but I was not able to able to pivot the responses. By using the same logic for responses I pivoted those as well in the looping.
UAUITSBI
Premium Member
Premium Member
Posts: 117
Joined: Thu Aug 13, 2009 3:31 pm
Location: University of Arizona

Post by UAUITSBI »

Thanks for the response rschirm. I have tried with pivot enterprise stage in which I was able to pivot the questions but couldn't pivot the responses as they are getting pivoted along with the questions as they are interrelated.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

You may have achieved this using transformer looping but what did you actually achieve? Not the desired end result. As already noted, it's a horizon pivot of columns to rows.
-craig

"You can never have too many knives" -- Logan Nine Fingers
priyadarshikunal
Premium Member
Premium Member
Posts: 1735
Joined: Thu Mar 01, 2007 5:44 am
Location: Troy, MI

Post by priyadarshikunal »

As you are hard coding Q1A Q1B Q1C, do you have only 3 columns you need to pivot? then why not just take 3 output from and transformer and funnel it back?

And as already noted multiple times by Craig, Its a horizontal pivot.
Priyadarshi Kunal

Genius may have its limitations, but stupidity is not thus handicapped. :wink:
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

59 columns... but still.
-craig

"You can never have too many knives" -- Logan Nine Fingers
priyadarshikunal
Premium Member
Premium Member
Posts: 1735
Joined: Thu Mar 01, 2007 5:44 am
Location: Troy, MI

Post by priyadarshikunal »

dang, missed that 59 column part in second post :oops:
Priyadarshi Kunal

Genius may have its limitations, but stupidity is not thus handicapped. :wink:
Post Reply