Let’s assume that we have table with data like this bellow :
and we want to view data like this
for that we can write query like this
|
1 2 3 4 5 |
SELECT SUM(IF(category='1991', value, 0)) as '1991', SUM(IF(category='1992', value, 0)) as '1992', SUM(IF(category='1993', value, 0)) as '1993' FROM dynamic_crosstab; |
But, what we can do if the data going bigger and category added, e.g. 1995, 1996. Yes of course we can add
|
1 |
SUM(IF(category='1993', value, 0)) as '1995' |
once again but, how if we have many category value which are similary. i think this is a waste, so ot will usefull if we write crosstab dynamicly. let’s check it out
|
1 2 3 4 5 6 7 8 9 10 |
CREATE DEFINER = 'root'@'localhost' PROCEDURE usefull.dynamic_crosstab() BEGIN SELECT GROUP_CONCAT( DISTINCT CONCAT("SUM( IF( category ='", category,"', value ,0 )) as '", category, "'")) FROM dynamic_crosstab into @crosstab_column; set @query = concat("SELECT ", @crosstab_column, " FROM dynamic_crosstab"); prepare stmt FROM @query; execute stmt; END |
the query above will transform all category value into column, wich distinct will prevent the redundance. just keep in mind and hope this helpfull.
gorokbek meleennnnnnnnnnnn
apaan gorokbek?
hahhahahahaha ga gaul banget sih