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 #:
9 of 9
Last update:
‎04-04-2019 03:28 AM
Updated by:
 
Labels (2)
Comments
Five Stars

Hi, there,

 

I want to transpose my file. Format is below:

Input File: It is a tabulation delimited file

IDOC Number     00000012321

Name                  John Lewis

Net Weight          0.000

Weight Unit         KGM

 

Output File: 

IDOC Number;Name;Net Weight;Weight Unit

00000012321;John Lewis;0.000;KGM;

 

How to do it in Talend?

Community Manager

Hi @stuti_206,

Please ask your question on the appropriate Community discussion forum, where you will get help from Talend staff and other users. If you need further assistance, please contact Talend Support.

HTH,

Alyce