Skip to Content

How to convert rows to columns in BO Data Services

This blog will discuss about the usage of Reverse Pivot Transform to convert row values of a table/file format to column values.

Reverse_Pivot Transform:

Reverse_Pivot transform creates a new column for every value that is specified as a pivoted column. Hence to summarize, this transforms converts row values to column values.

Let us take an example and see how the Reverse Pivot transform works. Steps to be followed:

1) Login to the BO Data services designer.

2) Create a test project say PRJ_TEST.

3) Create a test job say TEST_JOB.

4) Create a workflow say WF1 (This step is optional, user can directly place a Dataflow).

5) Drag and drop a dataflow, and name it as DF2.

6) Create an excel file with columns for e.g.  Name, Year, Bonus (Excel is used only for demo purpose, user can use table from the datastore connected). Example data available in below screen shot.


7) Import the excel file in the BO Data services designer. (This step is required if the source is Excel file)

8) Drag and drop the excel file & Reverse_Pivot Transform on the data flow.

9) Create a temporary table say tablename as REVERSEPIVOT (as target), into the data store configured on user’s system.

10) The below screen shot displays the source (excel file), Reverse_Pivot transform and the temporary table REVERSEPIVOT (target). Join the Source, Reverse_Pivot transform and the target table.

Image with Excel File as source, Reverse Pivot to transform data, REVERSEPIVOT table as target:


11) Following setting is required to be done at the Reverse_Pivot transform level, options available under Reverse_Pivot transform:

1) Non-pivot columns -> List of columns that needs to be displayed as it is in the target.

2) Pivoted columns -> The column data which the user wants to transfer into the same row. User can define some default value for the pivoted column  selected. 

3) Output Column ->

     a) Pivot axis column: Based on the column selected under pivot axis column, the columns will be created in the target table.

     b) Duplicate value: Incase the dataset will hold duplicate values then the user can select to either fetch row, or last row, or can also abort the row.

     c) Axis value: The value of the pivot axis column that will be representing a particular set of output column. Atleast one value should exists in the Axis column for a selected pivot axis column.

4) Column Prefix-> User can prefix column names for the transferred data into the new column.

12) The below screen shot displays the use of Reverse_Pivot transform for the excel file source.


12) Validate the job and then execute the job.

13) Check the resultant data. The below screen shot displays the output of the target table, where rows are converted to columns.


Hope the example is helpful to understand the usage of Reverse Pivot Transform.

You must be Logged on to comment or reply to a post.
  • Hello  Radika Rao,

    Here in your example .

    Year,Bound are one set ,which you have converted in to   2008_bonus , 2009_bonus ..etc columns.

    along with above set , If  have one more set Date_type , Date_Value set … which will be converted BOD,DOJ

    Example rows:-

    Date_Type      Date Value  

    —-         ————–

    DOB  , 01-01-1999 

    DOJ ,  01-01-1998

    I mean like below when we comibine 2 sets

    Name    Year     Bonus     Date_Type         Date Value

    ——-     ——-     ——–       ————-         ——————

    A01       2008    1000         BOD              01-01-1999

    A01       2009    2000         DOJ               01-01-1998

    which i convert to columns as below

    2008_Bonus       2009_Bonus               DOB                  DOJ

    1000                      2000                   01-01-1999      01-01-1998

    in source if i have sequence column , i use squence column as axis to reverse pivote. which is very much comfortable.

    But we dont have sequence column. 

    What column we have to use as axis column to do this ?. 

    • Hello D K,

      You can you the Date_Type column as the axis column and then you can rename the final output column name as per your requirement. Hope it helps.

      • if i use date_type as axis column then , that will convert only 1 set like below.

                       DOB                  DOJ

                    01-01-1999      01-01-1998

        That will not give result like below , i mean converting 2 sets at a time.

        2008_Bonus       2009_Bonus               DOB                  DOJ

        1000                      2000                   01-01-1999      01-01-1998