Converting columns to rows using the Pivot transform

Objectives

After completing this lesson, you will be able to:

  • Convert columns to rows using the Pivot transform

The Pivot Transform

The Pivot transform creates a new row for each value in a column that you identify as a pivot column.

It changes the way the relationship between rows is displayed. For each value in each pivot column, Data Services produces a row in the output data set as shown in the figure. You can create pivot sets to specify more than one pivot column.

For example, produce a list of discounts by quantity for certain payment terms. List each type of discount as a separate record, instead of displaying each discount type as a different column.

Reverse Pivot Transform

The Reverse Pivot transform reverses the process, converting rows into columns.

The output includes the non–pivoted columns, a column for the sequence number, the data field column, and the pivot header column.

Pivot Transform Options

The table lists the options available with the Pivot transform:

Pivot Transform Options

OptionDescription

Pivot Sequence Column

Assign a name to the sequence number column. For each row created from a pivot column, Data Services increments and stores a sequence number.

Non-Pivot Columns

Select columns in the source to show in the target without modification.

Pivot Set

Identify a number for the pivot set. Define a group of pivot columns, a pivot data field, and a pivot header name for each pivot set.

Data Column Field

Specify the column to contain all of the pivot column values.

Header Column

Specify the name of the column that contains the pivoted column names. The header column lists the names of the columns where the corresponding data originated.

Pivot Columns

Select the columns to be rotated into rows. Describe these columns in the Header column and describe the data in these columns in the Data field column.

Let's Try It

Let me guide you through the use of the pivot transform:

Log in to track your progress & complete quizzes