Merging SQL Result rows together

Question

I am working on an EAV storage system for storing schemaless meta data about an object, and am now working on a way to have it searchable, but have most of the processing done by the Database Server.

The table of the EAV is:

`entityID` - INT(11)
`entity` - VARCHAR(128)
`attribute` - VARCHAR(128)
`value` - BLOB

This is my statement:

SELECT 
    `entity`,
    (CASE WHEN `attribute` = 'bob' THEN `value` ELSE NULL END) `bob`,
    (CASE WHEN `attribute` = 'dean' THEN `value` ELSE NULL END) `dean`
FROM `eav`

which returns a nice set of rows (as expected) like this:

+----------+------+------+
|  entity  | bob  | dean |
+----------+------+------+
| testEnt  | foo  | NULL | // attribute = bob
+----------+------+------+
| testEnt  | NULL | NULL | // another test attribute
+----------+------+------+
| testEnt  | NULL | NULL | // another test attribute
+----------+------+------+
| testEnt2 | foo  | NULL | // attribute = bob
+----------+------+------+
| testEnt2 | NULL | foo  | // attribute = dean
+----------+------+------+

but when I attach GROUP BY (Entity), the result turns into this:

+----------+------+------+
|  entity  | bob  | dean |
+----------+------+------+
| testEnt  | foo  | NULL |
+----------+------+------+
| testEnt2 | foo  | NULL |
+----------+------+------+

so using the HAVING syntax after that stops working. Is there a way so that the result returned is:

+----------+------+------+
|  entity  | bob  | dean |
+----------+------+------+
| testEnt  | foo  | NULL |
+----------+------+------+
| testEnt2 | foo  | foo  |
+----------+------+------+

Answer 1

Apply an aggregate function with the CASE and the GROUP BY. Since the values are strings you can use either a MAX() or MIN() to return the result:

SELECT 
    `entity`,
    Max(CASE WHEN `attribute` = 'bob' THEN `value` ELSE NULL END) `bob`,
    Max(CASE WHEN `attribute` = 'dean' THEN `value` ELSE NULL END) `dean`
FROM `eav`
group by `entity`

See SQL Fiddle with Demo

In the event, you have an unknown number of attribute values to return, you can use a prepared statement to get the result:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when attribute = ''',
      attribute,
      ''' then value end) AS ',
      attribute
    )
  ) INTO @sql
FROM eav;

SET @sql = CONCAT('SELECT entity, ', @sql, ' 
                  FROM eav 
                   GROUP BY entity');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo

The result for both will be:

|   ENTITY | BOB |   DEAN |
---------------------------
|  testEnt | foo | (null) |
| testEnt2 | foo |    foo |