Pivot and Unpivot Data Transformation with Matillion

Data analytics and business intelligence thrive on the ability to transform data into actionable insights. An influential technique for reshaping data is pivot and unpivot transformation. Keep reading to learn how Matillion enables users to conduct pivot and unpivot transformations easily. 

Pivot and unpivot transformations serve as fundamental techniques for reorganizing data between a long format to a wide format (pivot) or vice versa (unpivot). These transformations are particularly useful for summarizing and aggregating data, making it easier to analyze and visualize. 

• Pivot Transformation:

This involves rotating rows into columns, typically to summarize data or perform aggregations. For example, converting sales data from individual transactions to a summary table with sales totals for each product category.

Pivot Transformation in Matillion

Here's what the data looked like originally: 

Original data, before pivot

And after the pivot operation: 

Pivoted data

• Unpivot Transformation: 

In contrast, unpivot transformation involves rotating columns into rows. This is useful for normalizing data or converting aggregated data back to its original format. For instance, transforming a summary table of sales totals by product category back into individual transaction records. 

Leveraging Matillion for Pivot and Unpivot Transformations Matillion ETL provides a user-friendly interface and powerful functionality for performing pivot and unpivot transformations on your data.

Unpivot Transformation in Matillion

Here's what the data looked like originally: 

Original data, before unpivot

And after the unpivot operation:

Unpivoted data

Let’s delve into how you can accomplish these tasks using Matillion

Pivot Transformation with Matillion:

1. Data Source Configuration: Begin by connecting Matillion to your data source, whether it’s a database, cloud storage, or another source.

2. Data Ingestion: Use Matillions components to ingest your raw data into the platform, ensuring that its formatted correctly for the pivot transformation.

3. Pivot Component: Utilize a Pivot component to specify the columns you want to pivot, the values to aggregate, and the resulting pivot columns. Configure any additional settings, such as aggregation functions and groupings.

4. Mapping and Transformation: Map the input columns to the pivot component and define any additional transformations or calculations you require.

5. Output Configuration: Choose your desired output destination, whether its a database table, file storage, or another destination. Configure any necessary settings for the output format.

6. Execution and Validation: Run the job to execute the pivot transformation and validate the results to ensure they meet your expectations. 

Unpivot Transformation with Matillion:

1. Data Source Configuration: Similar to the pivot transformation, start by connecting Matillion to your data source and ingesting the raw data into the platform.

2. Unpivot Component: Utilize an Unpivot component to specify the columns you want to unpivot and define the resulting output columns.

3. Mapping and Transformation: Map the input columns to the unpivot component and define any additional transformations or calculations required.

4. Output Configuration: Choose the output destination and configure the settings accordingly.

5. Execution and Validation: Run the job to execute the unpivot transformation and validate the results for accuracy.

Pivot and unpivot transformations are invaluable techniques for reshaping and restructuring data to suit your analytical needs. With Matillion, performing these transformations has never been easier. Whether you’re summarizing data, aggregating values, or normalizing data structures, Matillion provides the tools and functionality to streamline the process and unlock actionable insights from your data.

Try it yourself

Download example pipelines from the Matillion Exchange that you can run to try out Data Transposing / Pivoting in your own environment.

Start leveraging the power of pivot and unpivot transformations with Matillion today!