Create Pivot Tables in MySQL with MySQL Pivot Table functionality

Pivot table is a neat feature that allows for managing data and making relations and dependencies look obvious. It is achieved by transforming rows into columns and organizing them the way you want. With a help of a few clicks, you can create a pivot table in MySQL to group, sum, sort, and aggregate data to present it in a report format.

Large amounts of data can be a tall order. But it can also be a snap, and MySQL Pivot Table is what makes it possible. Generating pivot tables in MySQL is now intuitive - you start from dragging tables in Query Builder using mouse, then pivot the executed query, and visualize the data via user-friendly interface. In MySQL, pivot function helps you rearrange the data and see it from many perspectives depending on your specific needs, especially when numeric data is involved. Enjoy the crosstab-free experience to see a full picture as a part of dbForge Studio for MySQL.

  • Customization flexibility
  • Report-ready tables
  • Database analysis is sharp and simple
  • Graphical and easy-to-grasp metrics
  • Every value can be summarized regardless of its position

Creating a Pivot Table

To kick things off, you need to execute a query that will be the analysis subject and right-click on the data grid, select Send, and click on Pivot Table. Or you can simply hover over the Database menu and select Pivot Table from it.

Keep in mind that not all tables are well-suited for pivoting. The best-case scenario is when your data contains columns with duplicated values that can be grouped and numeric values that can be calculated for both grand and custom totals.

Once you drag the desired table from Database Explorer to the Pivot Table field, the Data Source tree will appear in the upper right corner of the screen. You will be able to drag data elements from Data Source to the main field and choose their positioning, rows, and columns.

Creating a Pivot Table

Creating a Chart in Pivot Table

Needless to say, seeing data in charts is handy and visual. dbForge Studio lets you do that in several clicks to visualize and compare dependencies. To enable the Chart view in the Pivot Table document, you need to do the following steps:
  • Once you created a pivot table, click on the Show Chart icon at the pivot table toolbar
  • Select which exact data should be displayed in chart using Ctrl+Click
  • Select Chart options in the Chart View toolbar or simply go to Chart Designer
Creating a chart

Reordering Fields

Reordering is the key element for making conclusions about data dependencies - and drag-and-drop functionality is the way to go. A simple mouse drag will rearrange fields according to your needs and make it look the most suitable way.

The Drag row fields here and Drag column fields here fields is where you drop data to transpose rows to columns in MySQL. They can always be swapped in any direction or hidden from the Pivot Table by right-clicking the data element and selecting Hide or by dragging it away from the Pivot Table window.

Reordering fields

Sorting and grouping fields

Pivot tables give a clean grasp over your data thanks to sorting and grouping capabilities.

You can sort data in descending or ascending order by click on the Sort button and choosing a preferred option.

To group your fields, you need to hover over the executed query tree view at the upper right corner of the window. Then, right-click on the desired menu, select Group by, and choose the grouping parameter for allowing easy data control.

Sorting and grouping fields

Filtering fields

The Filter functionality lets you choose which data should be displayed in your pivot table. Field headers contain Filter buttons that show you the options for checking database values.

You can also add your own filter using the filter all the data option. To do that, simply drag a required field from the Data View tree to the Filter area and adjust it in a preferred manner.

Filtering fields

Using Summary Functions to Calculate Custom Totals

Summary calculates custom totals of rows and columns for values - they will be shown in separate rows and columns depending on the selected data and highlighted to differ from the regular values.

By default, this MySQL pivot function counts the sum of all values even after you add new rows and columns. To modify the existing Summary Function or to add new instances, you need to go to the Data Source view, right-click on the required field, select Properties, and click on the Summary field. You will be able to choose between:
  • Sum of all values
  • Average of values
  • Count of values
  • Maximum value
  • Minimum values
  • Standard deviation (counted over subset or entire data)
  • Population variance (counted over subset or entire data)
Summary options

Pivot table with dynamic columns

Each time you modify the number of unique column values in your query, the Pivot Table will reflect those changes once you execute it. To do that, you won't need to drag data elements from Data Source tree, just use the SET command to choose their position.

You can modify the Edit SQL Text field while working with Pivot Table using Split Layout at the left bottom corner of the window. To see how MySQL pivots dynamic columns, you can save the SQL file after the query execution and open in the Start Page tab from Recent Files.

Search table data

Conclusion

dbForge Studio for MySQL provides functionality to quickly create pivot tables from any data in your database. After creating a pivot table, you can manipulate its fields - be it reordering, sorting, grouping, or filtering. You can also create a visual chart of the data and calculate custom totals using the Summary functions.

dbForge Studio for MySQL

Multifunctional MySQL GUI

Availability in the editions of dbForge Studio for MySQL

Feature

Enterprise
Professional
Standard
Express
Pivot Tables
Yes
Yes
None
None