A difficult query for me

I have the next table:

CREATE TABLE `alarms_9_2015` (
  `time` datetime NOT NULL,
  `key` varchar(24) DEFAULT NULL,
  `point_name` varchar(64) DEFAULT NULL,
  `message` varchar(60) DEFAULT NULL,
  `value` varchar(16) DEFAULT NULL,
  KEY `ALARMS_9_2015_index` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

And have some records like:

+------------------+---------+------------+------------------------------+----------+
| time | key | point_name | message | value |
+------------------+---------+------------+------------------------------+----------+
| 22/09/2015 01:08 | 1085001 | A | CAMBIO ESTADO DE DISPOSITIVO | ABIERTO |
| 22/09/2015 01:09 | 1085001 | A | RETORNO A NORMAL | CERRADO |
| 22/09/2015 02:09 | 1006057 | B | CAMBIO ESTADO DE DISPOSITIVO | ABIERTO |
| 22/09/2015 02:11 | 1085001 | A | RETORNO A NORMAL | CERRADO |
| 22/09/2015 05:11 | 1012032 | C | CAMBIO ESTADO DE DISPOSITIVO | ABIERTO |
| 22/09/2015 05:11 | 1012032 | C | RETORNO A NORMAL | CERRADO |
+------------------+---------+------------+------------------------------+----------+


And I want a query that give the next table:

+--------+------------------+------------------+
| equipo | ABIERTO | CERRADO |
+--------+------------------+------------------+
| C | 22/09/2015 02:09 | 22/09/2015 05:11 |
| B | 22/09/2015 05:11 | |
| A | 22/09/2015 01:08 | 22/09/2015 01:09 |
| A | | 22/09/2015 02:11 |
+--------+------------------+------------------+


Some help?

You need to first isolate the rows with 'ABIERTO' and 'CERRADO' and then do a FULL JOIN between them. Since MySQL has not implemented FULL join, there are various ways to achieve such a join. We will need the distinct values of (key, point_name) pairs, so the easiest way seems to be to get first these values and then do two LEFT joins, one to 'ABIERTO' and another to 'CERRADO' values:

SELECT
    d.point_name AS equipo,
    a.time       AS abierto,
    b.time       AS cerrado
FROM
    ( SELECT DISTINCT `key`, point_name
      FROM alarms_9_2015
    ) AS d
  LEFT JOIN
    alarms_9_2015 AS a
      ON  a.value = 'ABIERTO'
      AND a.key = d.key
      AND a.point_name = d.point_name
  LEFT JOIN
    alarms_9_2015 AS b
      ON  b.value = 'CERRADO'
      AND b.key = d.key
      AND b.point_name = d.point_name
ORDER BY
    COALESCE(a.time, b.time) ;

A composite index on (key, point_name, value) would improve efficiency.

StackExchange Database Administrators Q#115871, answer score: 3