MySQL – Odenktools

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

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;

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

SUM(IF(category='1993', value, 0)) as '1995'

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

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

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.

Category: MySQL

Dynamic Crosstab With Store Procedure