I've edited your example and I used WITH ROLLUP, CASE and FIELD statements to sort and make this:
Information:
mysql> SELECT * FROM test.tblAnnualData;
+----------+---------+------------+------+-----------+
| REPORTER | PARTNER | NET_WEIGHT | YEAR | COMMODITY |
+----------+---------+------------+------+-----------+
| Egypt | Canada | 5 | 2010 | wheat |
| Germany | UK | 1 | 2011 | wheat |
| Mexico | France | 5 | 2011 | wheat |
| Norway | USA | 2 | 2012 | wheat |
| Peru | France | 3 | 2011 | wheat |
| Spain | USA | 3 | 2010 | wheat |
+----------+---------+------------+------+-----------+
6 rows in set (0.00 sec)
Dynamic Query:
SET @@group_concat_max_len = 500000;
SET @QUERY1 = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT(" SUM(CASE WHEN PARTNER = '",PARTNER,"' THEN NET_WEIGHT ELSE 0 END) AS '",PARTNER,"'")
ORDER BY PARTNER ASC)
INTO @QUERY1
FROM tblAnnualData;
SET @QUERY1 = CONCAT("SELECT
REPORTER,
TOTAL,
USA,
France,
Canada,
UK
FROM (SELECT
IFNULL(REPORTER,'TOTAL') AS REPORTER,
SUM(NET_WEIGHT) AS TOTAL,",@QUERY1," FROM tblAnnualData
WHERE COMMODITY = 'wheat'
#AND Year = 2011
GROUP BY REPORTER WITH ROLLUP) AS A
ORDER BY FIELD(REPORTER,'TOTAL') DESC,
TOTAL DESC,
REPORTER ASC;");
PREPARE QUERY1 FROM @QUERY1;
EXECUTE QUERY1;
It is the same as this query:
SELECT
REPORTER,
TOTAL,
USA,
France,
Canada,
UK
FROM (SELECT
IFNULL(REPORTER,'TOTAL') AS REPORTER,
SUM(NET_WEIGHT) AS TOTAL,
SUM(CASE WHEN PARTNER='USA' THEN NET_WEIGHT ELSE 0 END) AS USA,
SUM(CASE WHEN PARTNER='France' THEN NET_WEIGHT ELSE 0 END) AS France,
SUM(CASE WHEN PARTNER='Canada' THEN NET_WEIGHT ELSE 0 END) AS Canada,
SUM(CASE WHEN PARTNER='UK' THEN NET_WEIGHT ELSE 0 END) AS UK
FROM tblAnnualData
GROUP BY REPORTER WITH ROLLUP) AS A
ORDER BY FIELD(REPORTER,'TOTAL') DESC,
TOTAL DESC,
REPORTER ASC;
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:
mysql> SET @@group_concat_max_len = 500000;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @QUERY1 = NULL;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT GROUP_CONCAT(DISTINCT CONCAT(" SUM(CASE WHEN PARTNER = '",PARTNER,"' THEN NET_WEIGHT ELSE 0 END) AS '",PARTNER,"'")
-> ORDER BY PARTNER ASC)
-> INTO @QUERY1
-> FROM tblAnnualData;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SET @QUERY1 = CONCAT("SELECT
"> REPORTER,
"> TOTAL,
"> USA,
"> France,
"> Canada,
"> UK
"> FROM (SELECT
"> IFNULL(REPORTER,'TOTAL') AS REPORTER,
"> SUM(NET_WEIGHT) AS TOTAL,",@QUERY1," FROM tblAnnualData
"> WHERE COMMODITY = 'wheat'
"> #AND Year = 2011
"> GROUP BY REPORTER WITH ROLLUP) AS A
"> ORDER BY FIELD(REPORTER,'TOTAL') DESC,
"> TOTAL DESC,
"> REPORTER ASC;");
Query OK, 0 rows affected (0.00 sec)
mysql> PREPARE QUERY1 FROM @QUERY1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Statement prepared
mysql> EXECUTE QUERY1;
+----------+-------+------+--------+--------+------+
| REPORTER | TOTAL | USA | France | Canada | UK |
+----------+-------+------+--------+--------+------+
| TOTAL | 19 | 5 | 8 | 5 | 1 |
| Egypt | 5 | 0 | 0 | 5 | 0 |
| Mexico | 5 | 0 | 5 | 0 | 0 |
| Peru | 3 | 0 | 3 | 0 | 0 |
| Spain | 3 | 3 | 0 | 0 | 0 |
| Norway | 2 | 2 | 0 | 0 | 0 |
| Germany | 1 | 0 | 0 | 0 | 1 |
+----------+-------+------+--------+--------+------+
7 rows in set, 1 warning (0.00 sec)
mysql>
Try it in SQLFiddle
Best Answer
It is obvious, by how you have arranged the rows in your example, that there are two entities in the second table, each with its own set of attribute values.
However, in SQL it is a convention that rows in a table have no inherent order. Therefore, if you want the server to distinguish between the two sets, you need either
a column that would specify the row order (so that by using that order you could somehow determine where a new set of values starts):
or
a column that would serve as an entity identifier, so that it would be clear which set of values belongs to which entity (and, thus, on which row in the output it should end up):
The first option is certainly much inferior, because in order to get the desired result you would need to obtain some kind of entity identifier one way or another, and with the first option you would have to derive it somehow based on the order of rows. Note that you would probably be restricted to always storing the values in a specific order, and in particular it would be mandatory that values belonging to the same entity be stored in consecutive rows only.
With the second option you could store the values arbitrarily: the dedicated entity ID column would unambiguously determine which set of values the row should belong. Your query would then be very similar to what you already have, you would only need to add
entityidto the GROUP BY: