MySQL : create a dynamic pivot table

This tutorial aims to realize a dynamic pivot table for MySQL.

What is a pivot table?

The pivot table, also known as crosstab, is a technique used to convert a vertical table into a horizontal one. By aggregating data, lines of the vertical table become columns of the horizontal one. The pivot is a technique used to generate human-readable reports or to ease queries on vertical tables.

Example of pivot

For marketing needs, I want to record everything about my website's users: visit time, browser, OS, source and so on...

Here is the table:

A vertical table
CREATE TABLE IF NOT EXISTS `tracking` (
  `user` VARCHAR(127) NOT NULL,
  `date_visit` datetime NOT NULL,
  `key` VARCHAR(127) NOT NULL,
  `value` VARCHAR(127) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `tracking` (`user`, `date_visit`, `key`, `value`) VALUES
('Nico', '2022-10-26 18:10:00', 'browser', 'chromium'),
('Nico', '2022-10-26 18:10:00', 'os', 'ubuntu'),
('Nico', '2022-10-26 18:10:00', 'source', 'google'),
('Obi-Wan', '2022-10-26 19:20:00', 'browser', 'firefox'),
('Obi-Wan', '2022-10-26 19:20:00', 'os', 'mac os x'),
('Obi-Wan', '2022-10-26 19:20:00', 'source', 'emailing_campaign'),
('Nico', '2022-10-27 15:45:00', 'browser', 'chrome'),
('Nico', '2022-10-27 15:45:00', 'os', 'fedora'),
('Nico', '2022-10-27 15:45:00', 'source', 'direct_access');

In this table there is 2 users:

• Nico visited twice the website: first time with chromium on ubuntu, second time with chrome on fedora.
• Obi-Wan only came once with firefox on Mac OS.

For a clearer vision, we will pivot this vertical table to: user | date_visit | browser | os | source.

To achieve this result, we will need to group by "user" and "date_visit" and use an aggregate function on "value": GROUP_CONCAT, COUNT, MIN, MAX, SUM...

With GROUP_CONCAT
SELECT
    t.user,
    t.date_visit,
    GROUP_CONCAT(t.value, NULL) AS agreg_value,
FROM
    tracking t
GROUP BY
    t.user,
    t.date_visit;

/* Result :
Nico | 2022-10-26 18:10:00 | chromium,ubuntu,google
*/

We now have a horizontal table with a tote column. One stuff to know about GROUP_CONCAT: it ignores the NULL values. So, if in this column we want only the browsers, we can add a condition to concat "value" only if the column "key" is equal to "browser". The same goes for "os" and "source".

GROUP_CONCAT with an IF
SELECT
    t.user,
    t.date_visit,
    GROUP_CONCAT(IF(t.key = "browser", t.value, NULL)) AS 'browser',
    GROUP_CONCAT(IF(t.key = "os", t.value, NULL)) AS 'os',
    GROUP_CONCAT(IF(t.key = "source", t.value, NULL)) AS 'source'
FROM
    tracking t
GROUP BY
    t.user,
    t.date_visit;

/* Result :
Nico | 2022-10-26 18:10:00 | chromium | ubuntu | google
*/

Et voilà ! We pivoted the table.

In this example, we are dealing with only 3 values. For more complexe cases, we will try to dynamically build the query.

Step by step:

Dynamically build the columns of a pivot table
-- Step 1

SELECT CONCAT(
    'GROUP_CONCAT(IF(t.key = "',
    t.key ,
    '", t.value, NULL)) AS ',
    t.key
)
FROM tracking t;

/* Result :
GROUP_CONCAT(IF(t.key = "browser", t.value, NULL)) AS browser
GROUP_CONCAT(IF(t.key = "os", t.value, NULL)) AS os
GROUP_CONCAT(IF(t.key = "source", t.value, NULL)) AS source
GROUP_CONCAT(IF(t.key = "browser", t.value, NULL)) AS browser
...
*/

-- Step 2

SELECT DISTINCT CONCAT(
    'GROUP_CONCAT(IF(t.key = "',
    t.key ,
    '", t.value, NULL)) AS ',
    t.key
)
FROM tracking t;

/* Result :
GROUP_CONCAT(IF(t.key = "browser", t.value, NULL)) AS browser
GROUP_CONCAT(IF(t.key = "os", t.value, NULL)) AS os
GROUP_CONCAT(IF(t.key = "source", t.value, NULL)) AS source
*/

-- Step 3

SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'GROUP_CONCAT(IF(t.key = "',
        t.key ,
        '", t.value, NULL)) AS ',
        t.key
    )
)
FROM tracking t;

/* Result :
GROUP_CONCAT(IF(t.key = "browser", t.value, NULL)) AS browser,
GROUP_CONCAT(IF(t.key = "os", t.value, NULL)) AS os,
GROUP_CONCAT(IF(t.key = "source", t.value, NULL)) AS source
*/

Now we can build the whole query.

Full process
SET @COLUMNS = NULL;

SELECT GROUP_CONCAT(
        DISTINCT CONCAT(
            'GROUP_CONCAT(IF(t.key = "',
            t.key ,
            '", t.value, NULL)) AS ',
            t.key
        )
    ) INTO @COLUMNS
FROM tracking t;

SET @QUERY = CONCAT(
    'SELECT
        t.user,
        t.date_visit,
        ',@COLUMNS,'
    FROM
        tracking t
    GROUP BY
        t.user,
        t.date_visit'
);

PREPARE stmt FROM @QUERY;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;