Creating Pivot Tables in PostgreSQL

The Pivot Table functionality can help you aggregate, sort, organize, reorganize, group, sum or average data stored in a database to understand data relations and dependencies in the best possible way. With the tool, you have access to a whole new set of information in just a few clicks.

Pivot Table provides an interactive view of your data going significantly beyond the native crosstab function in PostgreSQL. With very little effort (and minimum queries) you can look at the same data from many different perspectives. This functionality comes in dbForge Studio for PostgreSQL along with other useful database management features. Try the tool and enjoy PostgreSQL pivoting without crosstab.

Extract the significance from a large, detailed data set
Slice and dice your database without a hitch
Make your data more comprehensive
Simplify data representation
Get all the metrics you need

Creating a Pivot Table

To create a pivot table, execute a query to select data you want to analyze and navigate to the query output. Right-click the data grid and select the Pivot table option from the Send to sub-menu. To add fields to the pivot table, simply drag them from the Data Source view to the required pivot table area.

Note:
Not all the data source tables are good for converting into pivot tables. The data source should contain a column with duplicated values, that can be grouped, and a column with numeric data, that can be used to calculate grand totals and custom totals.

Example of Creating a Pivot Table in dbForge Studio for PostgreSQL

Creating a Chart

With dbForge Studio for PostgreSQL, you can view pivot data as a chart. This helps visualize data dependencies and compare them. You can view the chart associated with the needed pivot data directly in the pivot table document by switching to the Chart view.

To create a Chart:

Having created the pivot table, click the Show Chart button on the pivot table document toolbar.
Select the data to be displayed as a chart on the Pivot Table view of the document.
Adjust the Chart options using the Chart view toolbar, shortcut menu, or Chart Designer.

Chart view in the pivot table helps visualize data dependencies and compare them

Reordering Fields

Changing the layout form of a pivot table is the key pillar in making data easier to read and scan for details. In just a few clicks, you can create as many pivot tables as you need.

Simply drag a field from one area of a pivot table to another to design its layout and organize data differently.

To push the functionality further, you can transpose rows to columns to display row field values in columns and column field values in rows. Gleaning information from a database for processing is now as simple as black and white.

Transpose rows to columns with Reordering Fields in the pivot table

Sorting and grouping fields

Data in a pivot table can be sorted and grouped to reveal relationships, patterns, and trends.

Sorting allows displaying data in ascending or descending order. Just click the Sort button on the field header and choose the sorting mode you need.

To better understand data dependencies, you can group data in a pivot table using various predefined group modes. To group values of a field, right-click it in the Data Source view and select a required group mode from the menu.

Utilize advanced sorting and grouping options in the Pivot Table functionality of dbForge Studio for PostgreSQL to control how data is displayed.

Sorted and grouped data in a pivot table to reveal relationships, patterns, and trends

Filtering fields

You can use a number of filters to display only the required data in your pivot table.

To filter the values of any field, click the Filter button on the field header and in the dialog box that opens select the values to be displayed. The data will be filtered according to the selected criteria.

You can use a filter field to filter all the data in the pivot table. Just drag a required field from the Data Source view to the Filter area in the pivot table and enjoy the result.

Pivot Table is a rich source of data for analysis and decision-making.

Using Summary Functions to Calculate Custom Totals

Pivot table offers you a number of summary functions that you can apply to required column or row fields to calculate custom totals for their values. Custom totals are displayed in additional rows or columns highlighted in color.

The sum values are calculated by default and you can see them immediately after adding row and column fields in a pivot table. You can also apply a different summary function if required:

Average of values
Count of values
Maximum value
Minimum value

Example of Using Summary Functions in the pivot table to Calculate Custom Totals

Conclusion

dbForge Studio for PostgreSQL has robust built-in capabilities for visualizing data via the Pivot Table functionality that doesn't require writing complex pivot queries in PostgreSQL. You can arrange and rearrange statistics in order to draw attention to useful information. You can group data into categories, filter data to include or exclude categories, construct PostgreSQL pivot tables with dynamic columns, and even build charts.

Availability in the editions of dbForge Studio for PostgreSQL

Feature

Professional

Standard

Express

Pivot Tables