How to Build Pivot Tables

Did you know that a pivot table allows you to quickly summarize your data based on a group, pivot, and aggregation columns? This summary might include sums, averages, or other statistics, which the pivot table splits the statistics is a meaningful way for different subgroups and draws attention to useful information.

Fig. 1: A pivot table showing the average sunshine hours for each city in each month. This table was constructed by applying the pivoting function to a dataset that contains at least one column for month (group column), one column for city (pivot), and one column for sunshine hours (aggregation column).

Would you like to know more about how to use the Pivoting node in KNIME Analytics Platform? This vlog features three videos showing you how to use the Pivoting node, how to apply basic aggregation methods, such as sum and count, statistical aggregation methods, and the aggregation methods available for columns of type Date&Time. We also how to apply multiple aggregation methods to one or more aggregation columns.

So, settle down in your chair and get started with the Pivoting Trilogy, starring the Pivoting node.

The Pivoting Node

The video below shows you how to build a pivot table that summarizes data using the Pivoting node. We explain the elements of a pivot table, i.e. groups, pivots, the aggregation method, and aggregation column and show how to make these settings in the configuration dialog of the Pivoting node.

Pivoting With Complex Aggregation Methods

This next video shows some advanced layouts of the pivot table using multiple aggregation columns, statistical aggregation methods, and aggregations to columns of type Date&Time. We show you how to apply multiple aggregation methods in the same pivot table, discuss aggregation methods for columns of the type Date&Time; we introduce a new aggregation method: Date range(day), and also the aggregation methods, mean, standard deviation and other statistical measures. Finally, we show that some aggregation methods automatically disable the option to include missing values.

Pivoting With Multiple Columns

The last video shows some advanced layouts of the pivot table with multiple pivoting and/or grouping columns and how to set the Pivoting node in order to achieve them. We also introduce the new Mode and (Unique) concatenate aggregation methods.

Further Resources

• What’s data aggregation? An explanation is available on this video.
• Examples for using the Pivoting node EXAMPLES Server: 02_ETL_Data_Manipulation/02_Aggregations/09_Examples_for_Using_the_Pivoting_Node
• Pivoting in Databases EXAMPLES Server: 01_Data_Access/02_Databases/05_Pivoting_in_Databases