Pivot stage output link columns

The following example illustrates how to specify what data is output by the Pivot stage.

The output link Columns tab contains a Sales column derived from the three input columns: JAN_Sales, FEB_Sales, and MARCH_Sales. The columns are as follows:
Table 1. Output columns
Column name Derivation SQL type Length Scale
CUSTID   Integer 10  
Last_Name LNAME VarChar 10  
Sales JAN_Sales, FEB_Sales, MARCH_Sales Decimal 10 2
Note: For any column, the data type documented in SQL Type must be the same as the data type in the target table.

The output column that is derived from a single input column is a key value. The key value is repeated in each row that results from the corresponding input row.

The maximum number of output rows that result from a single input row is determined by the output column that is derived from the most input columns. The three output rows of sales data that result from each input row in this example are as follows:

Table 2. Output Target Rows
CUSTID Last_Name Sales
100 Smith $1,234.00
100 Smith $1,456.00
100 Smith $1,578.00
101 Yamada $1,245.00
101 Yamada $1,765.00
101 Yamada $1.934.00

If the pivot includes any derivations with fewer than the maximum number of output rows but more than one row, the output row contains a null value for each column where a derivation is not available.

As an example, assume the customer is required to make payments on his account twice a year, in June and December. The source data might look like this:

Table 3. Payments Example
CUSTID LNAME JAN_Sales FEB_Sales MARCH_Sales JUN_Pay DEC_Pay
100 Smith $1,234.00 $1,456.00 $1,578.00 $6,298.00 $7,050.00
101 Yamada $1,245.00 $1,765.00 $1,934.00 $7,290.00 $7,975.00
Suppose the output link contains an additional derivation for payments:
Table 4. Output columns with payments details
Column name Derivation SQL type Length Scale
CUSTID   Integer 10  
Last_Name LNAME VarChar 10  
Sales JAN_Sales, FEB_Sales, MARCH_Sales Decimal 10 2
Payments JUNE_Pay, DEC pay      

The output data in the target rows after the pivot looks like this:

Table 5. Output Data in Target Rows After Pivot
CUSTID LNAME Sales Payments
100 Smith $1,234.00 $6,298.00
100 Smith $1,456.00 $7,050.00
100 Smith $1,578.00 null
101 Yamada $1,245.00 $7,290.00
101 Yamada $1,765.00 $7,975.00
101 Yamada $1,934.00 null