Pivot in BODS

see more in our ebook

Pivot in Data Services:

This creates a new row for every value that you specify as a pivot column. Observe the icon, it says that it will convert column to rows.

Options:

• Pivot_Sequence : It creates a sequence for each row created from a pivoted column
• Non Pivot : List of columns specified here will be displayed as it is in the target.
• Pivot Sets : For each and every pivot set, you can define a set of columns. For each set you will be having a Header column and a Data column. Header column consists of all the pivoted columns, and Data column contains the actual data in the pivoted columns.
• Pivot Columns : Set of columns swiveled to rows.

Design Steps: There are 5 columns in the source table(Sno, Sname, Jan_sal, Feb_sal, Mar_sal). Below is the procedure to convert salary column values into rows-

• Drag the source table and target table from Datastore object library on to the workspace, drag the Pivot transform and place in between your source and target. Now, connect each object as shown in the below figure-

• Have a glance on source data-

• Double click on the Pivot Transform. Check the Pivot sequence name. The default name will be “PIVOT_SEQ”. You can change the name if required. The fields Sno and Sname will be loaded as it is. Therefore, drag these two columns on to the Non-pivotal list.
• Now drag all SAL Columns on to the Pivotal list. By default, PIVOT_DATA, PIVOT_HDR names will be be generated-

• Save the definition, now you can see, (SNO, SNAME, PIVOT_SEQ, PIVOT_HDR, PIVOT_DATA) columns in Schema Out.
• Come out from the Pivot transform by pressing BACK button on the standard tool bar.
• Save the Dataflow, validate it and execute the job.
• Check the resultant data-

As visible in the screen shot above, 3 records are generated for each row as input for Jan Sal, Feb Sal and Mar Sal.

PIVOT_SEQ is an integer assigned to each row generated. PIVOT_HDR will have the column names which were converted to rows.

Refer to the ebook for more details:

New Ebook – SAP BODS Step by Step