PIVOT ENTERPRISE Stage in Datastage

Pivot enterprise stage is a processing stage which pivots data vertically and horizontally depending upon the requirements. There are two types

  1. Horizontal
  2. Vertical

Horizontal Pivot operation sets input column to the multiple rows which is exactly opposite to the Vertical Pivot Operation. It sets input rows to the multiple columns.

Let’s try to understand it one by one with following example.

  1. Horizontal Pivot Operation.

Consider following Table.

Product Type

Color_1

Color_2

Color_3

Pen

Yellow

Blue

Green

Dress

Pink

Yellow

Purple

Step 1: Design Your Job Structure Like below.

Job structure for Pivot Enterprise stage

Configure above table with input sequential stage ‘se_product_clr_det.

Step 2: Let’s configurePivot enterprise stage’. Double click on it. Following window will pop up.

Select ‘Horizontal’ for Pivot Type from drop-down menu under ‘Properties’ tab for horizontal Pivot operation.

Step 3: Click on‘Pivot Properties’ tab. Under which we need to check box against ‘Pivot Index’. After which column of name ‘Pivot_Index’ will appear under ‘Name’ column also declare a new column of name ’Color’ as shown below.

Step 4:  Now we have to mention columns to be pivoted under ‘Derivation’ against column ‘Color’. Double click on it. Following Window will pop up.

Select columns to be pivoted from ‘Available column’ pane as shown. Click ‘OK’.

Step 5: Under ‘Output’ tab, only map pivoted column as shown.

Configure output stage. Give the file path. See below image for reference.

6

Step 6: Compile and Run the job. Let’s see what is happen to the output.

This is how we can set multiple input columns to the single column (As here for colors).

Vertical Pivot Operation:

Here, we are going to use ‘Pivot Enterprise’ stage to vertically pivot data. We are going to set multiple input rows to a single row. The main advantage of this stage is we can use aggregation functions like avg, sum, min, max, first, last etc. for pivoted column. Let’s see how it works.

Consider an output data of Horizontal Operation as input data for the Pivot Enterprise stage. Here, we will be adding one extra column for aggregation function as shown in below table.

Product


Color

Prize

Pen

Yellow

38

Pen

Blue

43

Pen

Green

25

Dress

Pink

1000

Dress

Yellow

695

Dress

purple

738

Let’s study for vertical pivot operation step by step.

Step 1: Design your job structure like below. Configure above table data with input sequential file ‘se_product_det’.

Step 2: Open Pivot Enterprise stage and select Pivot type as vertical under properties tab.

Step 3: Under Pivot Properties tab minimum one pivot column and one group by column. Here, we declared Product as group by column. Color and prize  as Pivot columns.Lets see how to use ‘Aggregation functions’ in next step.

Step 4: On clicking Aggregation functions required for this column for particular column following window will pop up. In which we can select functions whichever required for that particular column. Here we are using ‘min’, ’max’ and ‘average’ functions with proper precision and scale for Prize column as shown.

Step 5: Now we just have to do mapping under output tab as shown below.

Step 6: compile and Run the job. Lets see what will be the output is.

Output :

 

 

 


Leave a Reply

© 2017 Database ETL. All rights reserved.