Mysql Pivot rows into dynamic columns with unknown number of columns

You could do something along the following lines:

select 
    s1.grade, 
    s1.firstname as s1_fname, 
    s2.firstname as s2_fname, 
    s3.firstname as s3_fname,
    s4.firstname as s4_fname
from #grades as s1
-- Self join the table for second student
left outer join grades as s2
    on s1.grade = s2.grade 
    and s2.student_num = 2
-- Self join the table for third student
left outer join grades as s3
    on s1.grade = s3.grade 
    and s3.student_num = 3
-- Self join the table for forth student
left outer join grades as s4
    on s1.grade = s4.grade 
    and s4.student_num = 4
where s1.student_num = 1
order by s1.grade;

This joins the table to itself once for each set of columns (s1, s2, etc.). You could write some dynamic SQL to generate this based on the maximum student_num or just run select max(student_num) and make sure you have enough columns to catch all the values. (If you don't you'll need to add another join like the others).

Edit: This assumes that the grade and student_num columns combine to create a unique key. If you have duplicates (e.g. two students with grade 1, student_num 1), then you'll get duplicate rows in your results.

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