Visualize Oracle Data with Pivot Table

The Pivot Table functionality helps you rearrange rows and columns of data to a simplified table the way you want to understand data relations and dependencies. This page provides a brief overview of the pivot function of dbForge Studio for Oracle and describes the process of the pivot table creation.

Become a data management guru with dbForge Studio for Oracle, now featuring Oracle Pivot Table. The Oracle Pivot Table function converts large amounts of data into compact and informative summaries - pivot tables. In an intuitive and easy-to-use visual Query Builder, pivoting a table without crosstab becomes a simple and quick task: drag-and-drop tables, select columns, create JOINS, and set up filtering, grouping or sorting relations between tables in the tabbed editor, as well as sum, store, and generate data into a report. With a few clicks, you can aggregate the data and convert rows into columns.

  • Simplify data representation
  • Summarize data in an easy and flexible way
  • Customize totals for column or row values
  • Highlight the most important information in a report
  • Use a bunch of metrics to build pivot tables effectively and efficiently
  • Improve data analysis manipulation

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 Database Explorer, select required tables from your database and drag-and-drop them to the query document. They are displayed as boxes with columns. In the table boxes, select the corresponding check boxes you want to use.

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.

Pivot Table in Oracle - Creating a Table

Reordering fields

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

Simply drag-and-drop a field from one area of a pivot table to another in order to design its layout and to rearrange their data the way you need.

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

Pivot Table in Oracle - Reordering Fields

Sorting and grouping fields

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

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 the first letter.

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

Pivot Table in Oracle - Sorting Fields

Filtering fields

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

To filter values of any field, click Filter 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.

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.

Pivot Table in Oracle - Filtering Fields

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.

The Oracle pivot function counts the numeric values in the report using the SUM function by default. However, you can choose a different summary function:

  • Sum of all values
  • Average of values
  • Count of values
  • Maximum value
  • Minimum value
Pivot Table in Oracle - Custom Totals

Conclusion

The Pivot table functionality is a relevant tool for easy and agile manipulation and processing of large data sets. The tool helps you improve data analysis efficiency along with getting deeper into relations and dependencies, as well as extract the most useful information of data and generate it into a report. Along with the Pivot table tool, dbForge Studio for Oracle offers other useful database management and development features.

dbForge Studio for Oracle

Multifunctional Oracle GUI

Download

Availability in the editions of dbForge Studio for Oracle

Feature

Enterprise
Professional
Standard
Express
Pivot Table
Yes
Yes
None
None

We use cookies to provide you with a better experience on the Devart website. You can read more about our use of cookies in our Cookies Policy.
Click OK to continue browsing the Devart site. Be aware you can disable cookies at any time.