Converting rows to columns

author
Shicong Hong
EnrichVersion
6.4
6.3
6.2
6.1
6.0
EnrichProdName
Talend Real-Time Big Data Platform
Talend Data Services Platform
Talend Data Management Platform
Talend Big Data
Talend MDM Platform
Talend ESB
Talend Data Fabric
Talend Data Integration
Talend Big Data Platform
task
Design and Development
Design and Development > Third-party systems > File components (Integration) > Delimited components
Data Quality and Preparation > Third-party systems > File components (Integration) > Delimited components
Data Governance > Third-party systems > File components (Integration) > Delimited components
EnrichPlatform
Talend Studio

Converting rows to columns

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
ProcedureFirst Step

Create a simple Job as seen below:

The tPivotToColumnsDelimited 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.

This Job is also available in the attachments list.

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

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.