How to Create PIVOT Table and Pivot MySQL Data Without Complex Coding

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
  • Sharp and simple database analysis
  • Graphical and easy-to-grasp interface
  • Pivot data by just drag&drop
  • Summary functions

How to perform the Pivot function in MySQL

The concept of pivot and unpivot to display data differently stems from the NeXT platform. Pivot table is a commonly used feature of Excel. Unfortunately, Pivot function in MySQL doesn't exist.

To create a Pivot Table in MySQL, you will need a special MySQL Pivot Table generator tool—for example, dbForge Studio for MySQL contains advanced Pivot Table functionality and can be used to pivot and unpivot data in MySQL.

Another way to implement the Pivot Table function is to use a CASE statement. With it, you can create a simplified version of a pivot table with minimal functionality. We will explain how to pivot data in MySQL without any special tools below.

How to perform Pivot in MySQL

How to build a Pivot table in MySQL using the CASE statement

Some databases (for example, SQL Server) have native support for pivots, but MySQL, unfortunately doesn't support Pivot function. One of the methods to get around this and create a pivot table in MySQL is to use the SELECT statement with CASE expression.

For this, you need to you create a SELECT statement with the aggregate function, add a CASE statement inside that function, and then add a GROUP BY clause.

It is also possible to create a pivot table in MySQL using the IF statement inside the aggregate function.

MySQL pivot example

How to create Pivot Table in MySQL using dbForge Studio

In dbForge Studio for MySQL, to create a pivot table first you need to execute a query that will be the analysis subject and right-click on the data grid, select Send, and click 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

How to create 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

Pivot rows to columns by drag&drop

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 convert MySQL pivot rows to columns and rearrange fields according to your needs. Thus, vertical data will be transposed to horizontal to 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

Sort and group 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

Filter data in Pivot Table

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

Use 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 delivers a robust MySQL Pivot Table generator that allows you 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. All this functionality is provided by default in dbForge Studio for MySQL along with many other database management tools and features, for example, MySQL reporting tool.

dbForge Studio for MySQL

Multifunctional MySQL GUI

Download

Availability in the editions of dbForge Studio for MySQL

Feature

Enterprise
Professional
Standard
Express
Pivot Tables
Yes
Yes
None
None
dbForge Studio for MySQL

MySQL Pivot Table: Pivot Data in a Few Clicks!

dbForge of MySQL is a powerful and handy IDE for both MySQL beginners and experienced developers. Our GUI tool will help you to easily administrate MySQL database and automate daily routines.

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.