In this article, we will learn how to use SAP Data Services Pivot Transform. The Pivot transformation allows us to change how the relationship between rows is displayed. For each value in each pivot column, Data Services produces a row in the output data set. We can create pivot sets to specify more than one pivot columns. It basically convert Columns to Rows.

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 quarters for each of the stores. Find below the implementation Data Flow.

Data Flow Pivot

To solve the problem we are using the SAP Data Services Pivot transform. We first set the Pivot sequence column value as PIVOT_SEQ. So the sequence value will be 1 for QUARTER1 column, 2 for QUARTER2 column .. when being transformed to multiple rows. Select the STORE as the Non-Pivoted column. In this example we are dealing with only one Pivot set for QUARTER. Hence the Pivot set is set to 1. Next include all the input QUARTER columns as the Pivot Columns. Set the output Header column name as QUARTER and the corresponding Data fields as SALES.

Pivot Transformation

Finally we map the quarter to the Pivot Sequence in the Query transform. So for the first quarter sales value the corresponding QUARTER will be 1 and so on.

Data Flow- Query

Transform Options

  • 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 in the 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.


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.

  • Data Services Scenario Questions Part 1

    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 handle multiple input file in SAP Data Services

    In this tutorial, we will learn how we can use a single data flow to process multiple input files of same structure using SAP Data Services. Like before, we have included one hands-on video with the tutorial to help you see the technique in action

  • Data Services Metadata Query Part 1

    Data Services provides full access to the repository metadata tables and views for metadata analysis. To access this metadata either we can use SQL SELECT statements or use the metadata reporting from Management Console.

  • Data Services Flatfiles Tips

    Often we come across scenarios where we have the flat file definition in an excel sheet and we need to create corresponding File Format in SAP Data Services. Alternatively we import file format definition from a Sample Source file.

  • One Stop to SAP BODI/BODS

    BODI Business Objects Data Integrator or BODS Business Objects Data Services is a GUI workspace that allows to create jobs that extracts data from heterogeneous sources, transforms that data using built-in transforms and functions to meet business...

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

  • Using MERGE Transform in SAP Data Services

    This tutorial describes how to use MERGE transform in SAP Data Services (formerly known as BusinessObjects Data Services). A short video is also provided to provide hands-on.

  • Data Services Scenario Questions Part 3

    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.

  • Data Services Scripting Language

    We can use the Data Services Scripting Language to write scripts and custom functions to address complex logical expressions.

  • How to implement SCD Type 2 using History Preserve Transform in Data Services

    This tutorial teaches you how to use the "History Preserving Transform" in SAP Data Services (BODS) by demonstrating a practical use of this transform for the implementation of SCD Type 2. We have also provided hands-on video below so that you can...