Pivot Table Example Mysql


I am trying to return results for a matrix that represents the net weight that a country imports from another country.

I have managed to write a query that produces a pivot type table in mysql all data is coming from a single table. I have managed to dynamically create the columns (export countries) and sort rows by the total net weight imported by that country.

Where I am coming undone is sorting the export countries which are the columns. I am easily able to sort them alphabetically, however I need to somehow total each column when I dynamically generate that column and then sort by the sum of that column.

Here is an example of the table before the my query:

The shooting free movie full online 1967. This is an example of the structure that I am aiming to achieve.

In order to pivot a table in MySQL we must follow 4 steps. Select the columns of interest. Extend the base table with the columns that you picked. Group and aggregate the derived table.

  • Jan 29, 2018  A discussion of this topic can be followed on Dynamic Pivot Table Using MySQL. Creating a pivot table using MySQL is a quite easy task, the main obstacle to this process is when we want to sort the data that contain subtotal and total rows generated by WITH ROLLUP clause.
  • What is pivoting and how to pivot a table in MySQL. #### Watch the video in fullscreen at 720p for better quality ### Check this article by stratos for more explanation on pivoting a table http.

Here is the query I have produced so far which produces something similar to the above table, however not sorted correctly:

As you can see in the statement I am able to sort the columns by 'Partner' alphabetically but what I would like to achieve is sorting the sum of the column net_weight descending. So that the highest values are in the top left decreasing as you go right and down the table.

Can this be done? I have seen examples using WITH ROLLUP but can't seem to get anything to work.

Matt FrickerMatt Fricker

1 Answer

I've edited your example and I used WITH ROLLUP, CASE and FIELD statements to sort and make this:

Mysql Pivot Function


Dynamic Query:


It is the same as this query:


I used FIELD to sort by first when the field is TOTAL (that is the REPORTER aggregated field of the row generated by WITH ROLLUP), then I sort by the TOTAL of NET_WEIGHT. After that I finish with the REPORTER, just in case if some REPORTER has same TOTAL of other/others.

Testing the Dynamic Query:

Try it in SQLFiddle

2,7492 gold badges13 silver badges31 bronze badges

Not the answer you're looking for? Browse other questions tagged mysqlpivotsorting or ask your own question.