Technology

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 xtab2(`col_name` VARCHAR(32), `col_alias` VARCHAR(32),
             `col_from` VARCHAR(256), `col_value` VARCHAR(32),
             `row_name` VARCHAR(32), `row_from` VARCHAR(256), totals TINYINT)

DETERMINISTIC
READS SQL DATA
SQL SECURITY INVOKER

        -- 'Generate dynamic crosstabs - variant with GROUP_CONCAT'
BEGIN
        -- extend session buffer
        SET SESSION group_concat_max_len = 8192;

        -- 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 = IF(totals=1,CONCAT('SELECT ', row_name, ',n',
                 @xtab_query, 't',
                 IF(col_value = '1',
                        'COUNT(*)',
                        CONCAT('SUM(', col_value, ')')
                 ),
                 ' AS Totaln',
                 row_from),
                CONCAT(LEFT(
                CONCAT('SELECT ', row_name, ',n', @xtab_query),
                LENGTH(CONCAT('SELECT ', row_name, ',n', @xtab_query))-2), " ",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) 

Categories: Technology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s