MySQL Forums
Forum List  »  Newbie

Re: generate columns out of data
Posted by: Chris Stubben
Date: March 15, 2006 11:39AM

# You could save the query in a variable called @crosstab_query

delimiter //
CREATE PROCEDURE crosstab ()
begin
select concat('select ', group_concat(DISTINCT concat('sum(if(category="', category,'", amount, NULL)) as ', category) separator ' ,'), ' from tmp')
into @crosstab_query
from tmp;
prepare crosstab_query from @crosstab_query;
execute crosstab_query;
end
//

delimiter ;

# and execute the procedure using CALL.

CALL crosstab();
+------+------+------+
| A | B | C |
+------+------+------+
| 10 | 5 | 16 |
+------+------+------+

Check the link I sent for more details. You could generalize this and pass any table and column name as an argument as well. Check here.

http://www.futhark.ch/mysql/107.html

The procedure will remain until you drop it using drop procedure. Good luck.

Chris

Options: ReplyQuote


Subject
Written By
Posted
Re: generate columns out of data
March 15, 2006 11:39AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.