The Pivot transformation turns several rows of data into one, It makes a normalized data set into a less normalized but more compact version by pivoting the input data on a column value. For example, a normalized sales report includes shop name, month and sales amount usually has several rows for any shop, with each row for that shop showing sales amount for a different month. By pivoting the data on the month column, the Pivot transformation object can output a data with a single row per shop. That single row lists all sales by the shop, with month names shown as column names, and sales amount is shown as a value in the month column.

The following picture shows a data set before the data is pivoted:


Because we might have some new shops, some columns may contain null values.
When a dataset is pivoted, input columns perform different roles in the data transformation process. There are several possible options:
  • The column acts as the key or part of the key that identifies a set of records.
  • The column defines the pivot. The values in this column are associated with columns in the pivoted dataset.
  • The column contains values that are placed in the columns that the pivot creates.

The following picture shows a data set after the data has been pivoted on the Month column.

Multiple Fields Pivot  Example

To pivot data efficiently, which means creating as few records in the output dataset as possible, the input data must be sorted on the pivot column. If the data is not sorted, the Pivot transformation might generate multiple records for each value in the set key, which is the column that defines set membership. For example, if the dataset is pivoted on a Name column but the names are not sorted, the output dataset could have more than one row for each customer, because a pivot occurs every time that the value in Name changes.

Configuring the Pivot Transformation


  • To change UnPivot properties double click on the object.Pivot
  • Select appropriate Set Key, Pivot key and Pivoted value fields.Pivot Properties
  • Enter Pivot keys and Output Field names.Pivot Properties
Direct link, no registration required.
Swiss banking
Bank Of Oklahoma
Red Cross
Alta Pacific bank
Gas alberta
Royal Brunei
First Oklahoma bank


What customers say about us

  • swissbanking

    I used Advanced ETL Processor in 2 Enterprises for many business processes and Business automation (outside finance department). I did not find any other tool with so many functions and broad flexibility for that Price! If you need support for bugs or solution design you will get it very fast. Best Support I have ever seen.

    Lionel Albrecht
  • iqvia

    IQVIA and DB Software Laboratory (DBSL) partnered in 2010 and have been working in close cooperation ever since. Over this period of time DBSL software components formed an integral part of a large number of IQVIA applications currently used by over 20 UK NHS Trusts (Hospitals).

    Dmitry Dorsky,
  • xerox

    The product is easy to learn and once a developer understands the ETL way for solving the problem at hand, the developer's productivity will increase. Even our DBAs now uses the ETL software to quickly create solutions instead of SSIS or .SQL jobs.

    Daniel Fung
    Solutions Architect


This site uses cookies. By continuing to browse the site, you are agreeing to our use of cookies