How to use UnpivotRow component in Talend !!

In this post I will show you how to convert columns to multiple rows.  Step by Step post will help you to follow the steps to convert columns to rows in case if you get the input data as per the below
format.



This is Institution Input File:-
Intitution_ID;Intitution_Name;Address;City;Country;Course_1;Course_2;Course_3
101;NIIT;722 Bur Oak Avenue;Berlin;Germany;CS;EE;EC
102;AIIM;11 Collaroy 2093;Medrid;Spain;MBA;BBA;BCA
103;SRIT;223 Wellington 5011;London;UK;BSC;ME;MTECH

There is a specific talend component inbuilt for this purpose which will convert the columns to rows based on the key ID. Just follow the steps and you will be able to achieve your desired output.

Search for the tUnpivotRow component in the pallete area and Drag and drop the following components from the palette tFileInputDelimited,tUnpivotRow, and tLogRow.

Open the component properties of tUnpivotRow .

Click on Edit schema button, columns will be same as in the input file but in tUnpivotRow_1(Output) there will be one pivot key and pivot value column.And we want one more column to appear in output i.e Institution_ID so add it by clicking + button.

In the Row keys field if you want Institution_ID as a key i.e ID should be displayed for all the institutions so add it by clicking on + button.

When you Run the job excluding Institution_ID columns all other columns will be multiplied by 3 rows i.e there will be 3 rows * 7 columns = 21 rows will appear in the output.

Starting job how_to_use_unpivot at 14:41 21/05/2015.

Now after running the job you should be able to see the desired output in the screen as per below format, for the proper view you can choose the "table(print values in cells of a table)" which will print all the rows in proper table format.


[statistics] connecting to socket on port 3454
[statistics] connected
.----------------+--------------------+---------------------------.
|                               tLogRow_1                                       |
|=---------------+--------------------+-------------------------=|
|pivot_key            |pivot_value                |Institution_ID  |
|=-------------------+-------------------------+----------------=|
|Institution_Name|NIIT                            |101                 |
|Address               |722 Bur Oak Avenue  |101                 |
|City                     |Berlin                          |101                 |
|Country               |Germany                     |101                 |
|Course_1             |CS                               |101                 |
|Course_2             |EC                               |101                 |
|Course_3             |EE                               |101                 |
|Institution_Name|AIIM                           |102                 |
|Address               |11 Collaroy  2093       |102                 |
|City                     |Medrid                         |102                |
|Country               |Spain                           |102                |
|Course_1             |MBA                           |102                |
|Course_2             |BBA                            |102                |
|Course_3             |BCA                            |102                |
|Institution_Name|SRIT                            |103                |
|Address               |223 Wellington  5011  |103               |
|City                     |London                         |103               |
|Country               |UK                               |103               |
|Course_1             |BSC                             |103               |
|Course_2             |ME                               |103               |
|Course_3             |MTECH                       |103               |
'---------------------+--------------------------+----------------'
[statistics] disconnected
Job how_to_use_unpivot ended at 14:41 21/05/2015. [exit code=0]