Pivot component

Pivot Component

This component rotates 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 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

In our example, we start with two columns, named "value" and "thing". In the "value" column there is a list of values. In the "thing" column, there is a list of character data.

We want to create a new table, using the Aggregate Function "Sum" to add together the values under Pivot Values "One", "Two", and "Three", which can be thought of as identifiers.

"One" for example, contains the values of 10 and 3465, so the Sum function will add these together. In the new table, the new value will be 3475. Similarly, the values under "Two" are added together. The same applies to the values under "Three". Think of the Pivot component as using the same identifiers to group data together. The Aggregate Function applies to the chosen Pivot Values.