Complex SQL query on a single table

Question

There is a table:

event | id | timestamp
---------------------
event1 | 001 | 21-03-15
event2 | 001 | 22-03-15
event1 | 002 | 23-03-15
event2 | 002 | 24-03-15

What should be the query to display the result:

id | event1 | event2 |
----------------------
001 | 21-03-15 | 22-03-15 |
002 | 23-03-15 | 24-03-15 |

It seems to me that you first need to make a choice unique id:

SELECT id FROM test GROUP BY id;

And here is something like this:

SELECT timestamp
FROM   ... 
WHERE id IN (SELECT id FROM test GROUP BY id) AND event='event1';

Events are known in advance ('event1', 'event2'). If there are repeated events under the same id, with a different or the same timestamp, add columns to the result, for example:

id | event1 | event2 | event1 | event2 |
----------------------------------------
001 | 21-03-15 | 22-03-15 | 23-03-15 | 23-03-15 |
002 | 23-03-15 | 24-03-15 | NULL | NULL |

Answer 1

"" "-" :

SELECT id
     , min(CASE event WHEN 'event1' THEN timestamp END) AS event1
     , min(CASE event WHEN 'event2' THEN timestamp END) AS event2
FROM   test
GROUP  BY id
ORDER  BY id;

SQL Fiddle.

Answer 2

, :

SELECT A.id,GROUP_CONCAT(B.timestamp) AS event1, GROUP_CONCAT(C.timestamp) AS event2 FROM (select distinct id from test) A
   LEFT JOIN test B ON B.id=A.id and B.event='event1'
   LEFT JOIN test C ON C.id=A.id and C.event='event2' GROUP BY A.id

Answer 3

You can join records event1with records with event2:

SELECT t1.id as id, t1.timestamp as event1, t2.timestamp as event2 
FROM test t1 LEFT JOIN test t2
    ON t1.id = t2.id AND t1.event = 'event1' and t2.event = 'event2'

The request assumes that you always have event1, so LEFT JOIN was used.

If you need to handle cases where it is available only event2, you can emulate a FULL OUTER JOIN, as described in Full external registration in MySQL