Re: generate columns out of data
A few more details...
drop table tmp;
create table tmp(category char(1), amount int);
insert into tmp values ("A",3), ("A",6), ("A",1), ("B",3), ("B",2), ("C",6), ("C",10);
IF this is your crosstab query
select sum(if(category="A", amount, NULL)) as A ,sum(if(category="B", amount, NULL)) as B ,sum(if(category="C", amount, NULL)) as C from tmp;
+------+------+------+
| A | B | C |
+------+------+------+
| 10 | 5 | 16 |
+------+------+------+
You can use group_concat to generate the identical query without knowing the category names
select concat('select ', group_concat(DISTINCT concat('sum(if(category="', category,'", amount, NULL)) as ', category) separator ' ,'), ' from tmp') from tmp;
and then use a procedure to execute it. Or you could just run the query from a shell and re-direct it back to mysql
mysql -N -e 'select concat("select ", group_concat(DISTINCT concat("sum(if(category=\"", category,"\", amount, NULL)) as ", category) separator " ,"), " from tmp") from tmp' test | mysql -t test
+------+------+------+
| A | B | C |
+------+------+------+
| 10 | 5 | 16 |
+------+------+------+
Chris