Pivot Tables in MYSQL

I’ve found an awesome piece of code that I’ve used over the years that now seems to have disappeared of the net as the server hosting it is down. With Access, you can run a “Crosstab” query that will tabulate on a field and create a dynamic result set based on those fields. This is the equivalent using pivot tables in MySQL.

This is also known as a pivot table in Excel. Once you get beyond access, you lose this bit of functionality. This stored procedure brings it back.. It will work on MySQL version 5 and up. The original article can be found at http://www.futhark.ch/mysql/106.html

DELIMITER $
CREATE PROCEDURE xtab(`col_name` VARCHAR(32), `col_alias` VARCHAR(32), 
`col_from` VARCHAR(256), `col_value` VARCHAR(32), 
`row_name` VARCHAR(32), `row_from` VARCHAR(256)) 
DETERMINISTIC 
READS SQL DATA 
SQL SECURITY INVOKER 
COMMENT 'Generate dynamic crosstabs - variant with GROUP_CONCAT' 
BEGIN 

-- Some heavy double quoting (quoting of already quoted strings) involved here 
-- to build the query that builds the list of columns for the crosstab 
SET @column_query := CONCAT('SELECT CONCAT(GROUP_CONCAT(DISTINCT ', 
'\'\\tSUM(IF(`', `col_name`, '` = \\\'\', `', 
`col_name`, '`, \'\\\', ', `col_value`, 
', 0)) AS `\', `', `col_alias`, '`, \'`\' ', 
'SEPARATOR \',\\n\'), \',\\n\') INTO @xtab_query ', 
`col_from`); 

-- Uncomment the following line if you want to see the 
-- generated query to assemble the columns 
-- SELECT @column_query; 

PREPARE `column_query` FROM @column_query; 
EXECUTE `column_query`; 
DEALLOCATE PREPARE `column_query`; 

SET @xtab_query = CONCAT('SELECT `', `row_name`, '`,\n', 
@xtab_query, '\t', 
IF(`col_value` = '1', 
'COUNT(*)', 
CONCAT('SUM(`', `col_value`, '`)') 
), 
' AS `total`\n', 
`row_from`); 

-- Uncomment the following line if you want to see the 
-- generated crosstab query for debugging purposes 
-- SELECT @xtab_query; 

-- Execute crosstab 
PREPARE `xtab` FROM @xtab_query; 
EXECUTE `xtab`; 
DEALLOCATE PREPARE `xtab`; 
END$
DELIMITER ;

Now that we have the stored procedure in place, let’s spend some time using it. You can build the following tables and run the example below to get a sense for how it works.

CREATE TABLE employees (
        id INT auto_increment PRIMARY KEY,
        shop_id INT,
        gender ENUM('m', 'f'),
        name VARCHAR(32),
        salary INT
);  CREATE TABLE shops (
      shop_id INT auto_increment PRIMARY KEY,
      shop VARCHAR(32)
);

INSERT INTO shops (shop) VALUES ('Zurich'), ('New York'), ('London');  INSERT INTO employees (shop_id, gender, name, salary) VALUES (1, 'm', 'Jon Simpson', 4500),
(1, 'f', 'Barbara Breitenmoser', 4700),
(2, 'f', 'Kirsten Ruegg', 5600),
(3, 'm', 'Ralph Teller', 5100),
(3, 'm', 'Peter Jonson', 5200);

The two examples show that the procedure works for counting as well as for summing up values.

mysql> CALL xtab('gender', 'gender', 'FROM employees', 1, 'shop',
'FROM employees INNER JOIN shops USING (shop_id) GROUP BY shop');

shop	m	f	total
London	2	0	2
New York	0	1	1
Zurich	1	1	2

3 rows in set (0.03 sec)

mysql> CALL xtab('gender', 'gender', 'FROM employees', 'salary', 'shop', 'FROM employees INNER JOIN shops USING (shop_id) GROUP BY shop');

shop	m	f	total
London	10300	0	10300
New York	0	5600	5600
Zurich	4500	4700	9200

3 rows in set (0.03 sec)