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;Surgrey;Y
2;Name;Ross
2;Sex;M
2;Surgrey;Y
3;Name;Elise
3;Sex;F
3;Surgrey;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 this page to learn how to install a custom component.

Second Step

Create a simple Job as follows: 

 

  

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.

Icon

This Job is also available in the attachments list.

 

Third Step

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

Related Files

  File Modified
  ZIP Archive unpivotcolumnstorowsexample.zip Nov 19, 2012 byShicong Hong
  Text File dept.txt May 01, 2016 bynag j
Version History
Revision #:
1 of 1
Last update:
‎04-17-2017 06:14 PM
Updated by:
 
Labels (1)
Contributors