How To Pivot Data in Bold Data Hub

In this article, we will demonstrate how to import tables from a CSV file, to pivot the table using transformations, and move the cleaned data into the destination database using Bold Data Hub.

Follow the step-by-step process below.

Sample Data Source:

Step-by-Step Process in Bold Data Hub

Step 1: Open Bold Data Hub

Click on the Bold Data Hub.

Step 2: Create a New Pipeline

Click Add Pipeline in the left-side panel.

Enter the pipeline name and click the tick icon.

Step 3: Choose the Connector

Select the newly created pipeline and opt for the CSV connector. You can either double-click or click on the Add Template option to include a template.

Step 4: Upload Your CSV File

Click the “Upload File” button to select and upload your CSV file.

Step 5: Set the Properties

Copy the file path and paste it into the filePath property field.

Step 6: Save and Choose the Destination

Click Save, choose the destination, and confirm by clicking the Yes button.

Note: On-Demand Refresh will be triggered when the pipeline is saved. If needed, the pipeline can be scheduled in the Schedules tab.

Step 7: View Logs and Outputs

Click the pipeline name in the left-side panel and switch to the Logs tab to view logs.

Step 8: Apply Transformations

Go to the Transform tab and click Add Table.

Enter the table name to create a transform table for pivoting the data.

Note: The data will initially be transferred to the DuckDB database within the designated {pipeline_name} schema before undergoing transformation for integration into the target databases. As an illustration, in the case of a pipeline named “customer_service_data”, the data will be relocated to the customer_service_data table schema.

Pivot Tables for Reshaping Data

Overview

Pivot tables allow us to restructure data by summarizing it in a way that is easy to analyze.

Approach

We can create a transformation table to pivot the equipment’s status into separate column based on its status and the values are aggregated counts based on its status. This helps to identify purchase state and action need to be taken,

SQL Query for Creating a Pivot Table

SELECT row_number() OVER() AS id, * 
FROM (
   PIVOT (
Select "Hospital_ID",
"Location",
"Equipment",
"Status",
"Count"
       FROM healthcare_equipmentdata.data2
   ) ON "Status" 
   USING MAX(Count)
) ct

Preview:

Click on Run button to view the results of the given query.

After finishing the transformation, users should click the Save and Transform button. The tables will be transferred to the destination database during this process. Now the data will be transformed and moved to the destination.