DSXchange

Posted: **Fri Dec 19, 2014 10:58 am**

Hello,

A quick question on pivoting:

I have rows as below from the source:

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 !!

Posted: **Fri Dec 19, 2014 11:19 am**

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 !

Posted: **Mon Dec 22, 2014 11:16 am**

Is this a combination of Vertical pivoting and Horizontal pivoting ?

Posted: **Mon Dec 22, 2014 9:55 pm**

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.

Posted: **Mon Dec 22, 2014 10:36 pm**

Just use the enterprise pivot stage to do exactly what you want to do.

Posted: **Tue Dec 23, 2014 9:15 pm**

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.

Posted: **Tue Dec 23, 2014 9:30 pm**

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.

Posted: **Tue Dec 23, 2014 11:46 pm**

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.

Posted: **Wed Dec 24, 2014 4:40 am**

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.

Posted: **Wed Dec 24, 2014 8:27 am**

59 columns... but still.

Posted: **Mon Dec 29, 2014 5:42 am**

dang, missed that 59 column part in second post

DSXchange

Pivoting of rows

Pivoting of rows

Re: Pivoting of rows

Re: Pivoting of rows