Converting rows to columns

Overview

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

 

Environment

This procedure was written with:

  • Talend Open Studio for Date Integration release: 4.2.3
  • 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 Date Integration release: 4.2.3, 4.2.4, 5.0, 5.0.2, 5.1.1, 5.1.2, 5.2.0

 

Example

In this procedure, the following source file will be converted:

id question answer
1 Name Shong
2 Name Ross
3 Name Elise
1 Sex M
2 Sex M
3 Sex F
1 Surgery Yes
2 Surgery No
3 Surgery Yes

 

The rows are converted to columns as follows:

id;Name;Sex;Surgery
1;Shong;M;Yes
2;Ross;M;No
3;Elise;F;Yes

 

Procedure

First Step

Create a simple Job as seen below. This job is also available as an attachment.

1_010.png

 

2_008.png

 

The tPivotToColumnsDelimited component is used to convert the rows to columns. It requires at least three columns in the input schema: the Pivot column, the Aggregation column, and one or more Group keys.

 

Second Step

Execute the Job. The output file will be as follows:

id;Name;Sex;Surgery
1;Shong;M;Yes
2;Ross;M;No
3;Elise;F;Yes

 

Note: It is not currently possible to output the result as a data flow that doesn't require an output file. If necessary, you can use a tFileInputDelimited component to read the result back into the next Job from the output file created by tPivotToColumnsDelimited.

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