Turn your key-value pairs into a pivot table

A bit of sql today for a change - here's a situation you've probably found yourself in:

CREATE TABLE `key_value` (
  `item_id` int(11) NOT NULL,
  `key` varchar(55) NOT NULL,
  `value` text NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;



INSERT INTO `key_value` 
(`item_id`, `key`, `value`) 
VALUES
(1, 'key1', 'value1'),
(1, 'key2', 'value2'),
(1, 'key3', 'value3'),
(2, 'key1', 'value1'),
(2, 'key2', 'value2'),
(2, 'key3', 'value3');

Key - value tables are quite useful when you expect to have a lot of empty fields, or you may need to add additional fields down the road, but querying them can be a hassle.

In the example above, we have two items with 3 rows each. Normally we would set this up as a table with fields item_id, key1, key2, key3, but for some reason we've gone key-value. Now we'd like to see a simple table displaying this like an Excel spreadsheet.

Usually these pivot tables can be a real headache, but if you know which fields you are after, in can actually be quite easy.

Let's build it up step-by-step to see what we are doing. First, let's get a grid-like result:

SELECT
item_id,
(CASE WHEN `key` = 'key1' THEN `value` END) AS value,
(CASE WHEN `key` = 'key2' THEN `value` END) AS value2,
(CASE WHEN `key` = 'key3' THEN `value` END) AS value3
FROM key_value

We've got the basic outline of our result, but each item is still spread out over 3 rows. Where ever we are missing a value in the grid, a null is given.

Obviously, we need to group by, but if you just tag one onto the end of the query you see that without using AGGREGATE FUNCTIONS for the value fields, each grouped item is set with the values of the first row with that item id

SELECT
item_id,
(CASE WHEN `key` = 'key1' THEN `value` END) AS value,
(CASE WHEN `key` = 'key2' THEN `value` END) AS value2,
(CASE WHEN `key` = 'key3' THEN `value` END) AS value3
FROM key_value
GROUP BY item_id

So, we take advantage of those nulls we filled out the chart with earlier. MIN() and MAX() functions have two nice features that will give us exactly what we need - they work on strings as well as numbers, and they ignore null values, only looking at what is left.

So, the MAX() of "value" and 2 nulls is "value" like so:

SELECT item_id, MAX(summed_data.value1) AS value1, MAX(summed_data.value2) AS value2, MAX(summed_data.value3) AS value3
FROM
(SELECT
item_id,
(CASE WHEN `key` = 'key1' THEN `value` END) AS value1,
(CASE WHEN `key` = 'key2' THEN `value` END) AS value2,
(CASE WHEN `key` = 'key3' THEN `value` END) AS value3
FROM key_value) AS summed_data
GROUP BY item_id

Hope that helps!

Contact me