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.
1 Answer
I've edited your example and I used WITH ROLLUP, CASE and FIELD statements to sort and make this:
Mysql Pivot Function
Information:
Dynamic Query:
It is the same as this query:
Why FIELD?
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
oNareoNare