Query Chain using Pivot data Transformation

You are viewing Query Studio - Query Builder documentation for Data Trust v7.6 (latest).
Looking for the previous version? View v2024.06 documentation →

Data Trust › Query Studio › Query Builder

Query Chain using Pivot Transformation

Reshape data from rows to columns using the Pivot transformation in a Query Chain.

Overview

The Pivot transformation reshapes the data by converting unique row values in a specified column into individual columns — similar to a spreadsheet pivot table. This guide covers configuring the Pivot widget with key, value, and pivot column settings.

Note: Ensure all required connection profiles are configured in the Administration module before building queries or Query Chains in this workflow.

Prerequisites

• You are logged in to Data Trust with a Pro-User or Admin Pro-User role.
• A Query Chain with a data source containing the columns to pivot has been configured.

Steps

Path: Data Trust › Query Studio › Query Chain using Pivot Transformation

Figure 1: Query Chain Option

Navigate to the Query Studio module and select Query Chain Option to open the Query Builder workspace. This is the starting point for creating, editing, and managing queries in Data Trust.

Figure 2: Drag and drop RDBMS widget

Drag the Rdbms Widget widget from the palette and drop it onto the Query Chain canvas. Once placed, click the widget to open its configuration popup and complete the required settings.

Figure 3: Select connection and click on Navigator

Select the appropriate connection profile from the dropdown to link the query to its data source. Choosing the correct profile ensures the query reads from the intended database or system.

Figure 4: Select Table

The Navigator panel displays the available databases, schemas, and tables for the selected connection. Expand the tree to locate the required table, then click Select to add it to the query.

Figure 5: Click on Select

This step shows Click on Select in the Query Chain using Pivot Transformation workflow. Follow the on-screen prompts to complete this configuration step before proceeding to the next stage.

Figure 6: Transformations tab

The Transformations tab lists all available data transformation widgets — Filter, Projection, Aggregation, Join, Pivot, Unpivot, and more. Drag the required transformation onto the canvas and connect it between the data source and output widgets.

Figure 7: Drag and drop Pivot widget

Drag the Pivot Widget widget from the palette and drop it onto the Query Chain canvas. Once placed, click the widget to open its configuration popup and complete the required settings.

Figure 8: Configure and Save Pivot widget

Click Save to persist the query or configuration to the Query Explorer. A confirmation message appears once the save completes, and the query is available for reuse in Query Chains and scenarios.

Figure 9: RD Output Tab

The RD Output widget captures the final result set from the Query Chain for use in downstream processes. Configure the output name and format, then save the widget to make the results accessible to other scenarios and reports.

Figure 10: Drag and drop RD Output widget

Drag the Rd Output Widget widget from the palette and drop it onto the Query Chain canvas. Once placed, click the widget to open its configuration popup and complete the required settings.

Figure 11: Open and Save RD Output widget

Click Save to persist the query or configuration to the Query Explorer. A confirmation message appears once the save completes, and the query is available for reuse in Query Chains and scenarios.

Figure 12: Execute QC

Click Execute Now to run the query immediately and fetch the result set. The results appear in the Results tab, and the execution status is displayed in the progress indicator.

Figure 13: QC Monitor Mode

Monitor mode shows the live execution progress of the Query Chain as each widget processes data sequentially. Watch the progress indicators to confirm all stages complete successfully before reviewing the output.

Figure 14: Result

This step shows Result in the Query Chain using Pivot Transformation workflow. Follow the on-screen prompts to complete this configuration step before proceeding to the next stage.