What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

-- Download Reverse Pivot Transform as PDF --


Note on Reverse Pivot: This transform converts rows in to columns. It will group the data-set of different rows in to a single row with different columns. Observe the icon, it says that will convert rows to column.

  • Non-Pivot columns : List of columns specified here by you, those will be displayed as it is in the target
  • Pivoted columns: These columns containing data which you want to whirl.
  • Pivot axis columns: Based on this seq and unique axis value, columns will be created.
  • Duplicate value: Suppose, your source data-set contains duplicate values, then you can fetch first row, last row or you can abort the process.
  • Axis value: This represents a particular set of columns.
  • Column prefix: You can prefix column names for the rotated data.
  • Input data is grouped: If this option is checked, then RPivot reckons that the set of data which is coming from the source is sorted and grouped, read the data, then find the appropriate value for column prefix and then process the data.

Note: Sometimes, our record-set doesn’t contains axis value. For those scenarios, we need to compute a rank for every group.

Design Steps: I’m using (target table of pivot transform example) as a source which has (SNAME, PIVOT_SEQ, PIVOT_HDR, PIVOT_DATA) 4 columns.

  • Drag the source table from Datastore object library on to the workspace, drag the Reverse-Pivot transform and place after your source.
  • Now place a QRY step next to RP transform, after that place a target table object and now connect it each object.
rp_src
  • Click on RP transform, drag SNAME to non-pivotal column list. Drag PIVOT_HDR, PIVOT_DATA to pivoted columns list and provide default values as NULL.
  • In the Output columns sections, select PIVOT_SEQ as pivot axis column and select Duplicate value as First row/Last row(if you have any duplicate values)
  • then add Axis values and Column prefix to represent a particular set of data.
rp_trn
  • Now, save the definition, you can able to see the out put columns in RP transform out schema.
  • Come back to DF designer window click on QRY instance, drag the required coulmns onto out schema. Here I renamed all pivoted data columns to JAN_SAL, FEB_SAL, MAR_SAL.
rp_qry
  • Save the DF definition, validate it and execute it. Check the resultant dataset.
rp_tgt
What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

7 comments on “Reverse Pivot Transform

  1. I have a question. What to do if there are say 15 values in the pivot axis column, but you need to pivot it for only 10 values?

  2. For Eg you have a table
    col1 col2 col3
    a a1 24
    b a1 26
    c a1 27
    d a2 24
    e a2 26
    f a2 27
    g a3 24
    a a3 26
    i a3 27

    And i want to see :

    Col1 cell a1 a2
    a a1 24
    b a1 26
    c a1 27
    d a2 24
    e a2 26
    f a2 27
    g a3
    a a3
    i a3

  3. It was not formatted correctly, but i want to see a1’s values under a1 column a2’s valies under a2 column… for a3 row’s there should not be any value under a1 and a2 columns

  4. I wont mind if there is another column x which has values for rest of the col3’s values for non a1 and a2 values

Leave a Reply

Your email address will not be published. Required fields are marked *


*