Query Chain using unpivot 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 Unpivot Transformation

Normalise wide-format data from columns to rows using the Unpivot transformation.

Overview

The Unpivot transformation is the reverse of Pivot — it converts multiple columns into rows, normalising wide data into a long format. This guide covers configuring the Unpivot widget to select the columns to collapse into rows.

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 wide-format data source widget has been configured.

Steps

Path: Data Trust › Query Studio › Query Chain using Unpivot 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: RDBMS widget popup

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

Figure 4: Order_detals table

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

Figure 5: Select table and click on Select button

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 6: Save RDBMS 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 7: Drag and drop UnPivot widget

Drag the Unpivot 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: Select UnPivot Clolumns

The Select UnPivot Clolumns transformation reshapes the data by converting rows to columns (Pivot) or columns to rows (Unpivot). Specify the key column, value column, and the fields to pivot or unpivot, then save the widget.

Figure 9: Unpivot For and UnPivot By

The Unpivot For and UnPivot By transformation reshapes the data by converting rows to columns (Pivot) or columns to rows (Unpivot). Specify the key column, value column, and the fields to pivot or unpivot, then save the widget.

Figure 10: RD Output Option

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

Figure 11: Drag and drop RD Output

Drag the Rd Output 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 12: RD Output popup

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 13: Execute Button

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 14: Execution Compleated

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

Figure 15: Result

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