Pivot

Dark
Light

Article Summary

Share feedback

Thanks for sharing your feedback!

This article is specific to the following platforms - Snowflake - Synapse.

Pivot Component

The Pivot 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

Snowflake Properties
Property	Setting	Description
Name	String	A human-readable name for the component.
Aggregate Function	Select	Select which aggregate function to call to combine the grouped values from the Pivot Column. For more information, see Snowflake Aggregate Functions.
Pivot Column	Select	Specify 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	Specify 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.

Synapse Properties
Property	Setting	Description
Name	String	A human-readable name for the component.
Aggregate Function	Select	Select which aggregate function to call to combine the grouped values from the Pivot Column. For more information, see Aggregate Functions.
Pivot Column	Select	Specify 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	Specify 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.

Delta Lake Properties
Property	Setting	Description
Name	String	A human-readable name for the component.
Aggregate Function	Select	Select which aggregate function to call to combine the grouped values from the Pivot Column. For more information, read Aggregate Functions.
Pivot Column	Select	Specify 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	Specify 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 component 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.