Skip to Content
Author's profile photo Moumita Maity

Pivot Transformation in SAP BODS

Hi All

        Here I am going to share some of ideas related to Pivot Transforms in SAP BODS and the steps to create it as well

Pivot Transform is kind of ‘Data Integrator Transform’ present among various transforms in the Sap BODS(Designer).


Pivot Transformation is used to Convert Column Values of table\file into Row Values. Below is a simple example which would help us to

understand this very easily:

pivot ex.png

Here we can see how the column values containing “.NET “and “JAVA” are split into multiple rows.

It relieves us from the need of writing complex formulas, which are required when dealing with operations involving ‘Groups’ or ‘Aggregation functions’.          For e.g. If we want to calculate the total of amount incurred on the .NET and JAVA domains, we would need the across specific year:-

         Select Year, Sum (.NET) + Sum (Java)

However, If we use the ‘PIVOTED OUTPUT’, then our query would be easier:

       Select Year, Sum (Earning)


This Transform could be found if we click the ‘Transformation’ icon present under the ‘Local Object Library’ window in SAP BODS Designer as shown below:-

The icon is :-

/wp-content/uploads/2016/03/icon_911300.png(The Green color outline has been highlighted by me)



Following is the Excel input file used for this process:

pivot input file.png

Now we need to create a ‘Dataflow’ followed by uploading the Excel file via the ‘Format file’ tab present below the ‘Local objects Library’ window. Then we have to use the ‘Pivot Transform’, followed by populating the data in the temp table which is created by adding the custom ‘Data store’.

The Workflow for the process:

contents of pivot transform.png

(Please ignore the “PIVOT_OUTPUT.txt” file)


DF content.png

Checking the data of the PIVOT_INPUT file:

If we click on the Magnifier icon attached on the PIVOT_INPUT (source) then we will get the output below:-                                                                            data pivot inp.png



  • Pivot sequence column : The name we assign to the sequence number column. For each row created from a pivot column, Data Services increments and stores a sequence number. Data Services resets the sequence to 1 when creating an output row from an original row. For example, if the row corresponds to the first pivoted column, the sequence number for the row is 1.
  • Non-pivot columns : The input schema columns that appear in the output schema without modification.
  • Pivot set : The number that identifies a pivot set. For each pivot set, we need to define a group of pivot columns, a pivot data field, and a pivot header name. Each pivot set must have a unique Data field column and the Header column. Data Services automatically saves this information.
  • Pivot columns : A set of columns to be rotated into rows. Describe these columns desired output name in the Header column and corresponding data in these columns inthe Data field column.
  • Data field column : The name of the column that contains the pivoted data. This column contains all of the Pivot columns values. The data type of this column is determined by the data type of Pivot columns. If two or more Pivot columns contains different data types, Data Services converts the columns to a single data type i.e. the data type of the first column we added to the pivot set.
  • Header column : The name of the column that contains the pivoted column names. This column lists the names of the source columns where the corresponding data originated.


Initial pivot editor.png

We have to change the values for the ‘Data field column’ and ‘Header column’ and drag and drop the relevant fields  from the ‘PIVOT_INPUT’ into the ‘Non Pivot Column’ and ‘Pivot Column’ as shown below:-



Create a Customized Data store Object, followed by creation of Template table. The template table icon will be present in the Tool palette in the ‘Right hand side’ of the designer window.

Template table icon.png

Here ‘test’ is the Customized Data store object created :




Job executed successfully:-

pivot job.png


Temp_table op.png

For further references you can have a look over the ‘Business Object Data Integrator Reference Guide’ which is available at the following link:-


Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Kavin kumar G
      Kavin kumar G

      Hi ,


      Suppose everyday new column added should be changed as row.Is it acheivable in this?

      Author's profile photo Werner Dähn
      Werner Dähn

      No, the pivot columns cannot be dynamic. But isn't that requirement a bit odd? After 10 years you will have 3650 columns in the source table???

      Is that really the case or did I get your requirement wrong?


      Maybe a compromise might do the trick. You add *somehow" a view on top of your table and the view has always 365 column, the last 365 day-columns. Then the view is static and you can do use a pivot. And the problem of the dynamic content is at the point of "How can I update such view every day".

      Author's profile photo Kavin kumar G
      Kavin kumar G

      Hi Werner ,

      Consider for example :

      Country  11/04/2020  12/04/2020

      X              10                 20

      Y               1                   3

      And for the next day

      Country  11/04/2020  12/04/2020 13/04/2020

      X              10                 20              15

      Y               1                   3               100

      So even for the date 13/04/2020 the transformation need to apply.


      Author's profile photo Werner Dähn
      Werner Dähn

      Okay, so I understood correctly. Will new columns be added or old columns removed also?

      And my idea was to create a view on top of the table like

        "10/04/2020" as Day_2, 
        "11/04/2020" as Day_1, 
        "12/04/2020" as Day_0 
      from table;

      On the next day, the view is replaced with a new version

        "11/04/2020" as Day_2,
        "12/04/2020" as Day_1,
        "13/04/2020" as Day_0
      from table;


      This way the view structure remains constant and can be used in BODS. And creating a new view is easier than creating a new dataflow every day.


      Or maybe a view that has year, month and 31 columns, one per day?

      Author's profile photo Kavin kumar G
      Kavin kumar G

      I did not get the idea Please explain me.

      And it must hold records from Jan to Current date.And source I am using is csv which is everyday updated.

      Author's profile photo Werner Dähn
      Werner Dähn

      I would like to restart the discussion and start at the beginning. What do you have and what do you want to get.

      When a CSV file is the starting point, how does it look like, how does the file format definition in DS look like, who creates that DS object (as it seems to have more columns every day) etc...

      Author's profile photo Kavin kumar G
      Kavin kumar G

      Hi Werner,

      I have created seperate thread for the above :