Converting columns to rows

Overview

In this article you will learn how to convert columns to rows.

 

Environment

This procedure was written with:

  • Talend Open Studio for DI 5.1.0-r82787
  • Data Integration releases: 4.2.3, 4.2.4, 5.0.2, 5.1.1
  • JDK version: Sun JDK build 1.6.0_26-b03
  • Operating system: Windows XP SP3
Talend verified this procedure to be compatible with:
  • Talend Open Studio for Data Integration releases: 4.2.3, 4.2.4, 5.0.0, 5.0.1, 5.0.2, 5.1.1, 5.1.2, 5.2.0

 

Example

In this procedure the following source file will be converted:

Id;Name;Sex;Surgery
1;Shong;M;Y
2;Ross;M;Y
3;Elise;F;N

 

The columns become rows as follows:

Id;Question;Answer
1;Name;Shong
1;Sex;M
1;Surgery;Y
2;Name;Ross
2;Sex;M
2;Surgery;Y
3;Name;Elise
3;Sex;F
3;Surgery;N

 

To accomplish this task you can use a custom component called tUnpivotRow shared by daztop, a community member, on the Talend Exchange. Thanks, daztop, for sharing such a useful component.

 

Procedure

First Step

Install the custom component tUnpivotRow. Please refer to Installing a custom component for instructions.

 

Second Step

Create a simple Job as follows:

1_009.png

 

2_007.png

 

3_004.png

 

4_004.png

 

Because both columns (Pivot_key and Pivot_value) are read only in the tUnpivotRow component, you must change the column names in tMap to map to the target schema.

 

Third Step

Execute the Job, and tLogRow (display results with table model) will output the following result to the console:

 5_004.png

 

Version history
Revision #:
2 of 2
Last update:
‎06-22-2017 07:23 PM
Updated by:
 
Labels (1)
Tags (1)