Talend Pivot Columns

The Talend Pivot Columns or tPivotToColumnsDelimited field is used to pivot the given columns based on the Group by Column and the aggregate function. However, the Pivot Columns write the output in a file (not to any database). So, to use this Talend pivot data, you have to use another tFileInputDelimited and choose this output file.

In this Talend Pivot Columns example, we used the Pivot Source table in our SQL Database. The below Talend screenshot shows the data inside that Pivot source table. To get the same table, check the Tableau article.

Source Table

Talend Pivot Columns Example

First, drag and drop the DBInput, DBConnection, tDBCommit, and select the SQL Pivot Source table from the metadata.

Source Table

Next, drag the Talend tPivotToColumnsDelimited field from the palette to the job design.

  • Pivot Column: Please select the Column that you want to Pivot. These column values will convert into multiple columns.
  • Aggregation Column: Please select the Column that intends to aggregate. The aggregation will happen based on the Group by Column.
  • Aggregation Function: Choose the aggregate function from the list of valuable ones as per your requirement.
  • Group By: Please add the Input column that you want to use for grouping.
  • File Name: Please choose the file that you want to store this Talend pivot column output.
  • Row Separator: Please select your row separator. For instance, new line, tab space, or semicolon, etc.
  • Field Separator: Please add the column separator.
Talend Pivot Table Columns 3

Please connect the DBInput main row to tPivotToColumnsDelimited. Let me choose the Order_Year as the Pivot Column.

Talend Pivot Table Columns 5

Next, we selected the Order Quantity as the Aggregation Column and chose Sum as the aggregation function.

Talend Pivot Table Columns Aggregation function

Next, we added the Name as the Group By Column.

Add Field to Group By section

By default, Talend Pivot columns choose the default CSV file as the output. Here, we are choosing (creating) Unpivoted_Dara.csv file using the browse button. Next, please checkmark the Create Directory if does not exist option to make sure that the folder will be created. Next, we changed the Field Separator to a comma.

Talend Pivot Table Columns 9

Within the Pivot Columns Advanced setting, you can use advanced separators such as Thousand Separator, Decimal Separator, and some CSV options. These are common in all the File outputs.

Columns Advanced Settings

Let us run the Talend Pivot Columns Job.

Run the Talend Pivot Table Columns Job

You can see the result in the CSV output file.

UnPivoted Date

Let me open the Management studio and write a SQL Pivot query to compare the results, and they are the same.