MySQL Pivot: Design Complex PIVOT Queries Without Typing Any SQL

There is no PIVOT operation in MySQL, but you can write tedious SQL queries to generate cross-tables. Use just a fraction of your time instead, and visually design complex PIVOT queries for MySQL with our Data Xtractor. You do not even need to know SQL, as our queries will be automatically generated for you.

Design a MySQL GROUP BY Query

Install Data Xtractor and connect to our Chinook MySQL sample database. Then create and customize a new query, following these steps:

1. Right-click on the Customer table and select Add to New Built Query.
2. Drag and drop the Invoice shape item somewhere in the diagram area.
3. Uncheck any other shape item but the ones selected below for the Field row.
4. Click on the large Icons button, to hide all unused items.
5. Set Total item as a Sum aggregate, and use Group By for the rest.
6. Sort in descending order by FirstName and LastName, and ascending by BillingCountry.
7. Select a currency Format for Total.
8. Select No Pagination from the grid’s toolbar, and run the query.

This is how simple it is to visually design a GROUP BY query without writing one specific SQL line. The result will show the sums of all sales per customer, with their specific billing country names. Switch to SQL mode and look at the generated SQL query:

Switch to a MySQL PIVOT Query

You may want a cross table instead, with one of the Group By columns – like BillingCountry – transposed and displayed as column headers instead. No matter what supported database type you use, in Data Xtractor this is super-trivial: just switch your Group By column to Pivot, and this is it!

While still in design mode, the Pivot and the aggregated data columns will be emptied now. In result mode they will be hidden completely. Actual data will be rendered in new columns on the right. These are dynamic columns, with all distinct BillingCountry values as their headers. Make sure you have (no limit) set for your Pivot labels, to bring all values.

In SQL mode, you get two generated SQL queries. Last query is internally run to collect the distinct column header values:

The other top query is an emulated MySQL PIVOT query using these values:

Consider all the time you would waste to type these complex SQL queries by hand, especially if you have to do it frequently, for many MySQL Pivot values!

An Even More Complex MySQL PIVOT Query

In real life scenarios you may need to continue to filter data, use formulas and functions, and make this query even more complex than it is. Compare with visually designing your query with Data Xtractor, by doing this:

1. Concatenate FirstName with LastName, with a blank between.
2. Edit and change directly on the grid all column headers.
3. Exclude any customer whose name ends with Hämäläinen, using the Where clause.
4. Excluded all groups with less than $40 total Sales, using the Having clause.
5. Removed the currency Format and round up all numbers with a Round function call.
6. Check Hide Empty Pivot Columns, to hide countries like Brazil, with no sales at all.
7. Leave cells empty if Nulls.

See for yourself how more complex the generated queries have become now, and consider the time you would waste, and the expected SQL proficiency, if you would have to change them manually. At any time, after you’re done with your visual design, you may switch them to text queries and customize them the typical way.

Use Your MySQL Pivot Results to Make a Chart

One last touch, to see the power of our real-time analytics module, as Visual Query is embedded within Data Xtractor as well.

In the Chart builder, simply select Label for the Customer column, and Bar (from Bar Charts) for Sales. Maximize the resulting bar chart and play with toolbar’s configuration buttons, to get more or less something like the image below.

From the legend items, we filtered out anything else but the sales of just three countries. Remark Brazil and other countries with no sales are excluded from the chart as well.

Conclusion

• Our visual SQL query builder can emulate lots of features not native for a database system. Emulation of the complex and tedious PIVOT queries in particular is a spectacular feature, that could save you time, no matter how proficient you are in SQL.
• We can also show PIVOT queries with more than one aggregate. We also implement native PIVOT queries when supported. We emulate and support native UNPIVOT queries. We emulate the TRANSPOSE specific operation. We can redirect the result of all PIVOT columns to a chart, with one single assignment.
• There are tones of other SQL-related features not discussed here, please take the time to walk through the generated demo queries or our online knowledge base. You may instantly switch your complex generated queries to parameterized queries, to nested queries or queries using a WITH list, to queries using the syntax of another supported platform etc.
• Data Xtractor can save you huge in time and money. To visually design SQL queries for any supported database, instead of typing them by hand, is not just fast, reliable and more intuitive, but also fun.