How to convert rows to columns in BO Data Services

[Former Member]

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)

😎 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.