Talend Pivot Columns 9

Talend Pivot Columns

The Talend Pivot Columns or tPivotToColumnsDelimited field used to pivot the given columns based on the Group by Column and and the aggregate function. However, the Talend 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.

Talend Pivot Columns 1

Talend Pivot Columns Example

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

Talend Pivot Columns 2

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

Talend Pivot Columns 3

Please connect the DBInput main row to tPivotToColumnsDelimited.

Talend Pivot Columns 4
  • Pivot Column: Please select the Column that you want to Pivot. This 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.

Let me choose the Order_Year as the Pivot Column.

Talend Pivot Columns 5

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

Talend Pivot Columns 6

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

Talend Pivot Columns 7

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.

Talend Pivot Columns 8

Next, we changed the Field Separator as a comma.

Talend Pivot Columns 9

Within the Talend 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.

Talend Pivot Columns 10

Let us run the Talend Pivot Columns Job.

Talend Pivot Columns 11

And you can see the result in the CSV output file.

Talend Pivot Columns 12

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

Talend Pivot Columns 13