Convert a single row into multiple rows ( horizontally pivoting ) with Pivot stage ?

In this example, the Pivot Enterprise stage is set up to horizontally pivot some data.

You can generate a pivot index that will assign an index number to each row within sets of horizontally pivoted data. The following tables provide examples of data before and after a horizontal pivot operation.


Input Data
REPID,last_name,Jan_sales,Feb_sales,Mar_sales
100,Smith,1234.08,1456.80,1578.00
101,Yamada,1245.20,1765.00,1934.22
102,Xing,2190.89,1287.98,2054.55
103,Anderson,1498.09,1287.23,3298.76



Output Data
 REPID,last_name,Q1,Pivot_index
101,Yamada, 1245.00,0
101,Yamada, 1765.00,1
101,Yamada, 1934.00,2
103,Anderson, 1498.00,0
103,Anderson, 1287.00,1
103,Anderson, 3298.00,2
100,Smith, 1234.00,0
100,Smith, 1456.00,1
100,Smith, 1578.00,2
102,Xing, 2190.00,0
102,Xing, 1287.00,1
102,Xing, 2054.00,2

Design

a) This is the job design.

b) Input File settings

c) Input File columns

 

d) The following screen capture shows the settings for a horizontal pivot on the Pivot Definition tab of the Stage page.

e) For selecting the column on which we have to use Pivoting, follow below steps

i) First, create a new column, Q1 here, select SQL type, length, scale etc.

ii) Now come to derivation part, click on far right in the derivation column, it will browse the columns available as  input.

 iii) Now, select the column on which you need pivot, and click on > arrow to send in Selected Column

iv) Click OK, you will get the ScreenShot d.

f) Output Column mapping in Pivot stage

 

g) OutPut File columns

I hope, design is clear to you all. Please drop your comments if have any questions.