In this article, we will learn how to use SAP Data Services Reverse Pivot Transform. The Reverse Pivot transformation combines data from several rows into one row by creating new columns. For each unique value in a pivot axis column and each selected pivot column, Data Services produces a column in the output data set. It basically creates one row of data from several existing rows, i.e. to Pivot Rows to Columns.

This article is part of our comprehensive data services tutorial "Learning SAP Data Services - Online Tutorial", you may want to check that tutorial first if you have not already done so.

Implementation

Let us consider we have source data of sales for different stores based on quarters. We may want to transform the sales data based on the Stores for the four quarters. Find below the implementation Data Flow.

Data Flow Reverse Pivot

To solve the problem we are using the SAP Data Services Reverse Pivot transform. We already have sorted the source data based on the Non-pivot column SHOP. Next we select the SALES as the required Pivoted column and QUARTER column as the Pivot Axis. The quater values are basically 1,2,3 and 4. So the Axis values have been set accordingly.

Reverse Pivot Transformation

Finally we map the sales for the quaters for each shop accordingly in the Query transform. 1_SALES mapped to Quarter1, 2_SALES to Quarter2 so on.

Data Flow- Query

Transform Options

  • Non-pivot columns : The columns in the input schema that will appear in the output schema without any modifications.
  • Input data is grouped : Enable this option if the input rows are already sorted based on columns specified in the "Non-pivot columns" list. This will improve the performance of the transformation.
  • Pivoted columns : The columns containing data that we want to rotate or convert into the same row. A set of columns will be created for each unique values in the Pivot axis column.
  • Default value : The value stored when the rotated column has no corresponding data. The default is "null" if we do not enter a value. Do not enter a blank.
  • Pivot axis column : The column that determines what new columns are needed in the output schema. At run time, a new column is created for each Pivoted column and each unique value in this column.
  • Axis value : The value of the pivot axis column that represents a particular set of output columns. A set of Pivoted columns is generated for each axis value. There should be one Axis value for each unique value in the Pivot axis column.
  • Column Prefix : Text added to the front of the Pivoted column names when creating new column names for the rotated data. An underscore separates the prefix name from the pivoted column name.
  • Duplicate value : Action taken when a collision occurs. A collision occurs when there is more than one row with the same key and value in the Pivot axis column. In this case, we can select either the first row or the last row, or we can abort the transformation process.


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • Top 50 SAP Business Objects Data Services (BODS) Interview Questions with Answers

    Learn the answers of some critical questions commonly asked during SAP BO Data Services interview.

  • SAP BODS Cluster Installation

    This article is a step by step guide on how to configure SAP BODS for High Availability using Windows Cluster services. To take advantage of fail-over support for SAP BusinessObjects Data Services services in a Windows Clustering Environment,...

  • Auditing in SAP Data Services

    A proper data reconciliation process must be in place in any data Extraction-Transformation-Load (ETL) process. A successful reconciliation process should only indicate whether or not the data is correct. But data reconciliation is not easy....

  • Text Data Processing using SAP Data Services

    This article deals with Text Data Processing using SAP Business Objects Data Services with the intension of Text Analytics. SAP BODS provides a single ETL platform for both Structured and Unstructured data as well as Data Quality, Data Profiling...

  • Fools Guide to BODS - Introductory Tutorial

    This article is a guide to the readers who are totally ignorant about the tool SAP Business Objects Data Services. It is presented in a very simple way so that readers would be familiar with common terms, terminologies and would be able to run...

  • Learning SAP Data Services - Online Tutorial

    We are pleased to start this new tutorial page for SAP BusinessObjects Data Services (BODS). If you do not know SAP Data Services (BODS) yet but wish to master this ETL tool, you have come to the right place. Even if you are already familiar with...

  • Error Handling, Recoverability- SAP Data Services

    Error Handling, Recoverability are the important aspects of any ETL tool. Some of the ETL tools have some sort of in-built error-handling and automatic recovery mechanisms in place.

  • GROUP RANK in Data Services

    In this article, we will learn how to implement RANK and DENSE RANK operations on GROUP in SAP Data Services.

  • Data Services Scenario Questions Part 6

    In this tutorial we will discuss some scenario based questions and their solutions using SAP Data Services. This article is meant mainly for Data Services beginners.

  • How to use Lookup and Join in SAP Data Services

    In this tutorial, we will learn how to perform lookup and join in SAP BusinessObjects data services using the Query Transform. In the way, we will also see how to use some of the basic inbuilt functions that are provided in data services. A short...