Previous Next

Visualize Data by using Pivot Table in Oracle

Become a data management guru with dbForge Studio for Oracle, now featuring Oracle Pivot Table. Pivot Table is a tool that converts large amounts of data into compact and informative summaries - pivot tables. With a few clicks of your mouse your data can be rearranged the best way to understand data relations and dependencies.
Note: table designer does not generate pivot SQL queries that can be used outside of the program.


dbForge Studio for Oracle: Pivot Table
  • Drag and drop functionality
  • Custom filtering
  • Sorting and Grouping
  • Automatic function calculation
  • Custom and grand totals

See also

Creating a table

Pivot tables are created from Query Builder. Open a query document to select it as a data source for a pivot table. In the Database Explorer, select required tables from your database and drag-and-drop them to the query document. They are displayed as shapes with columns. In required table shapes select corresponding check boxes.

Pivot Table in Oracle - Creating a Table

Switch to the Pivot Table view to see the pivot table template. The Data Source view opens automatically with the fields specified in our query document. To add fields to the pivot table, simply drag-and-drop them from the Data Source view to a required pivot table area.

Reordering fields

Reordering fields is the key point in finding the best table layout to understand data dependencies and use them to your benefit.

Simply drag-and-drop a field from one area of a pivot table to another to rearrange their data. You can see the Oracle pivot table example on the screenshots.

Pivot Table in Oracle - Reordering Fields

You can easily swap row and column fields to display row field values in columns and column field values in rows. Compare the two screenshots.

Pivot Table in Oracle - Swapping Data

Sorting and grouping fields

Sorting data in a pivot table allows displaying it in ascending or descending order.

To sort values of any field, click the Sort button on the field header.

Pivot Table in Oracle - Sorting Fields

You can group data in an Oracle pivot table using various predefined group modes. Grouping values of some field is done with a single click of your mouse. It will appear as a new sub-field and you will be able to add it to a required area in your pivot table.

Values of date and time type can be grouped by years, months, quarters, weeks, days, etc. Numeric data can be grouped into numeric ranges. Text data can be grouped by first letter.

Pivot Table in Oracle - Grouping Fields

To remove grouping, select a required sub-field and press Delete.

Besides predefined group modes you can create your own group criteria using the Collection Editor dialog box.

Filtering fields

Various types of filtering can be applied to display only required data in a pivot table.

To filter values of any field, click the Filter button on the field header and in the opened dialog box select only required values that you want to display. The data will be filtered according to the selected criteria.

Pivot Table in Oracle - Filtering Fields

You can add a filter field to filter the whole data in the pivot table. Drag-and-drop a required field from the Data Source view to the Filter area in the pivot table. In the picture below the film rating filter was added. This way films in the list can be filtered by their rating.

Pivot Table in Oracle - Adding a Filter

Using summary functions to calculate custom totals

Pivot Table offers you a number of summary functions that can be applied to calculate custom totals for column or row values. Custom totals are displayed in additional rows or columns highlighted in grey.

Pivot Table in Oracle - Custom Totals

Availability in editions of dbForge Studio for Oracle

Features Express Standard Professional
Pivot Table No No Yes