Pivot component

Pivot component


This article is specific to the following platforms - Snowflake.

Pivot Component

This component rotates, or 'pivots' a table by turning the individual values from one column in the input expression into several columns, and aggregates results (where needed) on any remaining column values. In a query, it is specified in the FROM clause following the table name or subquery.

This component supports built-in aggregate functions.

The component can be used to transform a narrow table (e.g. sales, month) into a wider table (e.g. jan_sales, feb_sales).

Properties

Property Setting Description
Name Text The descriptive name for the component.
Aggregate Function Select Select which aggregate function to combine the grouped values from the Pivot Column.
Pivot Column Select Choose the column from the source table or subquery that will be aggregated. This requires an input component before configuring. It uses the column names from the input component.
Value Column Select The column from the source table or subquery that contains the values from which column names will be generated. This requires an input component before configuring. It uses the column names from the input component.
Pivot Values Values A list of values for the pivot column to pivot into headings in the query results.

Example 1

In this example, the Pivot component is connected to a Fixed Flow component.

 

The Fixed Flow component is configured with two columns: 'Payment', and 'Month'. The Values parameter highlights a sequence of payments made across the months January, February, March, and April.

 

When we sample the data via the Fixed Flow component, we have our two columns but the data is in a raw format. We're going to use the Pivot componenent to pivot the 'Payment' column and produce data samples that show first the overall sum of payments per month, and then the average payment for each month.

 

So, here we have the configured Pivot component. Our first Aggregate Function is 'Sum'. The second Aggregate Function will be 'Average'.

 

The next image shows our Pivot component data with an Aggregate Function set to 'Sum'. We can see that the payments for each month have been added together to give a final spending total. The named months have gone from acting as column data to column headers, with their payments segregated accordingly.

 

Now, in the next image, the Aggregate Function is set to 'Average' as mentioned earlier, and the sample shows the mean spend across each month.

Example 2

In this example, more data has been added to the 'Payment' column in the Fixed Flow component. Additionally, the table has two more columns. 'Paid', which has a value of either 'Yes' or 'No', and 'Payment_Type', delineating whether the payment was made by cash, debit card, or credit card.

 

The Pivot component's properties are configured to once again aggregate payments by month using a 'Sum' Aggregate Function.

 

Now, we have a table that separates payments by month in columnar order while having separate rows for paid and unpaid payments per 'Payment_Type'. In this example, we can see that a number of payments made via credit card in the month of April remain unpaid.