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 Example
First, drag and drop the DBInput, DBConnection, tDBCommit, and select the SQL Pivot Source table from the metadata.
Next, drag the Talend tPivotToColumnsDelimited field from palette to job design.
Please connect the DBInput main row to tPivotToColumnsDelimited.
- 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.
Next, we selected the Order Quantity as the Aggregation Column and chose Sum as the aggregation function.
Next, we added the Name as the Group By Column.
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, we changed the Field Separator as a comma.
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.
Let us run the Talend Pivot Columns Job.
And you can see the result in the CSV output file.
Let me open the Management studio and write a SQL Pivot query to compare the result, and they are the same.