Pivot rows into columns in MySQL, dynamically

Turn key/value rows into one column per key when you don’t know the keys in advance.

8 November 2014 · 1 min read

From the archive: first published on mohamedsharaf.net in November 2014. Rewritten and checked in 2026; some details may be out of date.

Key/value tables (like WordPress’s postmeta) store one row per attribute. For a report you often want one column per attribute instead. MySQL has no PIVOT keyword, so you build the query from the data:

SET SESSION group_concat_max_len = 100000;
SET @cols = NULL;

SELECT GROUP_CONCAT(DISTINCT CONCAT(
  'MAX(IF(m.meta_name = ''', meta_name, ''', m.meta_value, NULL)) AS `', meta_name, '`'))
INTO @cols
FROM post_meta;

SET @sql = CONCAT('SELECT p.id, p.name, ', @cols,
                  ' FROM post p LEFT JOIN post_meta m ON m.post_id = p.id GROUP BY p.id, p.name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Two cautions. Raise group_concat_max_len, or a long column list gets silently cut off. And because the attribute names become part of the SQL, they must come from a trusted list, never from user input, or this becomes an SQL injection.