Re: generate columns out of data
One last thing. You could run the statements without creating a procedure to check the timing. Most likely, the execute statement may run slowly, which only means you need to optimize the query.
mysql> select concat('select ', group_concat(DISTINCT concat('sum(if(category="', category,'", amount, NULL)) as ', category) separator ' ,'), ' from tmp') into @crosstab_query from tmp;
Query OK, 1 row affected (0.00 sec)
mysql> prepare crosstab_query from @crosstab_query;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> execute crosstab_query;
+------+------+------+
| A | B | C |
+------+------+------+
| 10 | 5 | 16 |
+------+------+------+
1 row in set (0.00 sec)