Pivoting Data Transformation Using 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:

Sample Data

Creating Pipeline

Learn about Pipeline Creation

Applying Transformation

Learn more about transformation here

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.